Jedi SAS Tricks: The DATA to DATA Step Macro

24

I was answering questions about SAS in a forum the other day, and it struck me how much easier it is to help folks if they can provide a snippet of data to go along with their program when asking others to help troubleshoot. This makes it easy to run their code on your own SAS session so you can help pinpoint the problem more quickly. It's easy to share your code on a forum with a quick copy and paste, but it's not so easy to share data. Encodings, O/S platforms, etc. all make a difference, so often you can't just attach a dataset, even if the forum accepts attachments - it wouldn't be usable. So how can I turn a SAS DATA set into a DATA step? Why, write an DATA set to DATA step macro, of course!

I solved this problem for myself a long time ago by writing a macro that takes a SAS data set and creates a DATA step which uses DATALINES to reproduce the data using code. I can then share the the code that produces the input data set as part of the program when I ask for help. I put this macro program in my SAS AUTOCALL path so it's always available when I need it. The macro name is data2datastep, and like most of the macros I write for my own use, it is self-documenting. Calling the macro with !help as the value of the first parameter - like this: %data2datastep(!help) - will get you syntax help in the SAS log.

A little about how the macro works: after all of the parameter checking is complete, the macro uses some SQL to gather information about the data set and put that information into macro variables for later use. Here is the SQL query that gets a space delimited list of the variable names which we will use to produce values for the DATALINES portion of our DATA step:

select Name
      into :varlist separated by ' '
   from dictionary.columns
   where libname="&lib"
     and memname="&dsn"
;

And here is the second query that gets a space delimited list of the variable names concatenated with a ':' and required informat, which we will use on the DATA step INPUT statement to read the values in from the DATALINES:

select case type
          when 'num' then 
             case 
                when missing(format) then cats(Name,':32.')
                else cats(Name,':',format)
             end 
          else cats(Name,':$',length,'.')
       end
      into :inputlist separated by ' '
   from dictionary.columns
   where libname="&lib"
     and memname="&dsn"
;

Finally I used a DATA _NULL_ step to write a DATA step. Sounds a little nefarious, doesn't it? :-) But it works nicely and the resulting DATA step program will produce a dataset which can be used for testing:

data _null_;
   file "&file" dsd;
   if _n_ =1 then do;
      put "data &lib..&dsn;";
      put @3 "infile datalines dsd truncover;";
      put @3 "input %superq(inputlist);";
      put "datalines4;";
   end;
   set &lib..&dsn(obs=&obs) end=last; 
   put &varlist @;
   if last then do;
      put;
      put ';;;;';
   end;
   else put;
run;

After the macro is compiled, %data2datastep(!help) produces this information in the SAS log:

NOTE: DATA2DATASTEP macro help document:
      Purpose: Converts a data set to a SAS DATA step.
      Syntax: %DATA2DATASTEP(dsn<,lib,file,obs>)
      dsn:  Name of the dataset to be converted. Required.
      lib:  LIBREF where the dataset resides. Optional.
      file: Filename for the DATA step produced. Optional.
            Default is create_&lib._&dsn._data.sas in the SAS default directory.
      obs:  Max observations to include the created dataset. Optional.
            Default is MAX (all observations)

NOTE:   DATA2DATASTEP cannot be used in-line - it generates code.
        Use !HELP to print these notes.

Now let's test that baby on some real data! I want to produce a DATA step that will create 5 rows of data from the SASHELP.CARS dataset. The macro call would be:

%data2datastep(cars,sashelp,,5)

The DATA step program is written to the file create_sashelp_CARS_data.sas. Here's what the code looks like:

data SASHELP.CARS;
  infile datalines dsd truncover;
  input Make:$13. Model:$40. Type:$8. Origin:$6. DriveTrain:$5. 
        MSRP:DOLLAR8. Invoice:DOLLAR8. EngineSize:32. Cylinders:32. 
        Horsepower:32. MPG_City:32. MPG_Highway:32. Weight:32.
        Wheelbase:32. Length:32.;
datalines4;
Acura,MDX,SUV,Asia,All,"$36,945","$33,337",3.5,6,265,17,23,4451,106,189
Acura,RSX Type S 2dr,Sedan,Asia,Front,"$23,820","$21,761",2,4,200,24,31,2778,101,172
Acura,TSX 4dr,Sedan,Asia,Front,"$26,990","$24,647",2.4,4,200,22,29,3230,105,183
Acura,TL 4dr,Sedan,Asia,Front,"$33,195","$30,299",3.2,6,270,20,28,3575,108,186
Acura,3.5 RL 4dr,Sedan,Asia,Front,"$43,755","$39,014",3.5,6,225,18,24,3880,115,197
;;;;

I can edit the code to change the LIBREF of the dataset to be produced to WORK. If there is sensitive information included, at this point I can easily anonymize it by editing the clear text values in the DATALINES portion of the DATA step. When I'm done editing, can test my code:

data WORK.CARS;
  infile datalines dsd truncover;
  input Make:$13. Model:$40. Type:$8. Origin:$6. DriveTrain:$5. 
        MSRP:DOLLAR8. Invoice:DOLLAR8. EngineSize:32. Cylinders:32. 
        Horsepower:32. MPG_City:32. MPG_Highway:32. Weight:32.
        Wheelbase:32. Length:32.;
datalines4;
Acura,MDX,SUV,Asia,All,"$36,945","$33,337",3.5,6,265,17,23,4451,106,189
Acura,RSX Type S 2dr,Sedan,Asia,Front,"$23,820","$21,761",2,4,200,24,31,2778,101,172
Acura,TSX 4dr,Sedan,Asia,Front,"$26,990","$24,647",2.4,4,200,22,29,3230,105,183
Acura,TL 4dr,Sedan,Asia,Front,"$33,195","$30,299",3.2,6,270,20,28,3575,108,186
Acura,3.5 RL 4dr,Sedan,Asia,Front,"$43,755","$39,014",3.5,6,225,18,24,3880,115,197
;;;;
title "&syslast";
proc print;
run;

The result looks good!
Dataset produced from code generated by the data2datastep macro

With the data2datasetp macro in my toolbox, I can easily post a little snip of data to go with my code whenever I'm asking for help in a forum.

As usual, you can download a zip file containing the complete SAS program and a copy of this article from the link below. Until next time, may the SAS be with you!
Mark

Link to the original macro program ZIP file: Jedi_SAS_Tricks_Data2DataStep.zip
Link to the latest update: Jedi_SAS_Tricks_Data2DataStepV2.zip

Share

About Author

SAS Jedi

Principal Technical Training Consultant

Mark Jordan (a.k.a. SAS Jedi) grew up in northeast Brazil as the son of Baptist missionaries. He served 20 years as a US Navy submariner, pursuing his passion for programming as a hobby. Upon retiring from the Navy in 1994, he turned his hobby into a dream job and has been a SAS programmer ever since. Mark writes and teaches a broad spectrum of SAS Foundation programming classes, and is proud to announce his first book, "Mastering the SAS® DS2 Procedure: Advanced Data Wrangling Techniques". When he isn’t writing, teaching, or posting “Jedi SAS Tricks” here on the SAS Learning Post, Mark enjoys playing with his grand and great-grandchildren, hanging out at the beach and reading science fiction novels. His secret obsession is flying toys – kites, rockets, drones – and though he usually tries to convince Lori that they are for the grandkids, she isn't buying it. Mark lives in Toano, VA with his wife, Lori, and their cat, the amazing Tiger Man. To connect with Mark, check out his SAS Press Author page, follow him on Twitter @SASJedi or connect on Facebook or LinkedIn.

24 Comments

  1. This is awesome Mark!!!
    I couldn't see an out of the box method to use this in SAS UE?
    I used the call as follows:
    %data2datastep(class,sashelp,/folders/myfolders/output.sas,10)

    I can modify the code to work specifically on SAS UE, but wondering if there was a way to make it work out of the box.

    • SAS Jedi

      Fareeza,
      Thanks for this awesome suggestion! I took a look at the problem, and found out that SAS University Edition sets the default path to a location where the user does not have write authority. However, there is an easy way to tell if you are running the macro in SAS University Edition: it automatically creates a GLOBAL scope macro variable named USERDIR when you log in. So I modified the macro to test for the existence of USERDIR and to use the value of USERDIR to set the path for your program file (unless you specified a fully-qualified file name).

      While I was testing, I found another bug in my original code. When testing for the presence of the FILE parameter, I was using &file to resolve the value instead of %superq(file). Having written a blog on this very issue back in 2011 (Jedi SAS Tricks: Macro Q functions), I was a little ashamed of myself...

      In any case, I modified the data2datastep.sas code to fix that bug and to be SAS University Edition-friendly. Just download the ZIP file again to get the updated version (program file modification date is March 13, 2016 at 10:43 am)

      May the SAS be with you!
      Mark

  2. Edward Ballard on

    Excellent! Now if we could just make a sticky in the SAS Community forums so people could find it easily for posting their data there.

  3. Since I have started to use macros I am becoming a MACROFANATIC, I cannot wait to try tomorrow it and tested...

    THANKS!!!

  4. @Mark Thanks for the follow up. I tested and it works fine now :) I've written a post for the forum, if it gets approved :)

  5. When I copy this macro into enterprise guide and feed it parameters %data2datastep(class,sashelp,/SAS Folders/My Folder/output.sas,10);
    It does not work. Am I missing something?

    • SAS Jedi

      Hey, David - please don't copy code from the text in the blog post. Although it looks pretty (and tempting) the HTML usually contains hidden characters that mess up the code when you try to execute it. Instead, download the zip file containing the SAS program from the link provided at the end of the article, unzip it, then open the SAS program in Enterprise Guide and submit that. It should work that way.

      If you still are having trouble, let me know. And it would be helpful to include information about the symptoms of the problem. How did you discover that the code didn't work? What error messages are produced in the SAS log?

      Stay SASy, my friend!
      Mark

  6. Pingback: Jedi SAS Tricks - Make This a Button in Base SAS - SAS Learning Post

  7. Pingback: Jedi SAS Tricks: DIY Tasks in SAS Studio - SAS Learning Post

    • SAS Jedi

      Hummm... what do you mean by this? Are you thinking the macro should check for a dataset label and re-create it if it exists? Or were you thinking of variable (column) labels instead?

      • SAS Jedi

        How about this newly modified version?
        http://blogs.sas.com/content/sastraining/files/2017/07/Jedi_SAS_Tricks_Data2DataStepV2-2.zip

        From the macro's self-documentation:
        Purpose: Converts a data set to a SAS DATA step.
        Syntax: %DATA2DATASTEP(dsn,lib,outlib,file,obs,fmt,lbl)
        dsn: Name of the dataset to be converted. Required.
        lib: LIBREF of the original dataset. (Optional - if DSN is not fully qualified)
        outlib: LIBREF for the output dataset. (Optional - default is WORK)
        file: Fully qualified filename for the DATA step code produced. (Optional)
        Default is create_&outlib._&dsn._data.sas in the SAS default directory.
        obs: Max observations to include the created dataset.
        (Optional) Default is MAX (all observations)
        fmt: Format the numeric variables in the output dataset like the original data set?
        (YES|NO - Optional) Default is YES
        lbl: Reproduce column labels in the output dataset?
        (YES|NO - Optional) Default is YES

Leave A Reply

Back to Top