topic Re: The DATA to DATA Step Macro in SAS Programming

The DATA to DATA Step Macro

actuarial — Thu, 31 Aug 2023 19:00:55 GMT

Update. Thanks for the clear and free of jargons explanation.

I am not sure if the first Macro in this post contain problems, but columns did not separate. The first Macro also did not work if there are too many columns

For example:

*data set you want to create demo data for;
%let dataSetName = WORK.QUERY_FOR_AVERAGE;
*number of observations you want to keep;
%let obsKeep = 1;


******************************************************
DO NOT CHANGE ANYTHING BELOW THIS LINE
******************************************************;

%let source_path = https://gist.githubusercontent.com/statgeek/bcc55940dd825a13b9c8ca40a904cba9/raw/865d2cf18f5150b8e887218dde0fc3951d0ff15b/data2datastep.sas;

filename reprex url "&source_path";
%include reprex;
filename reprex;

option linesize=max;
%data2datastep(dsn=&dataSetName, obs=&obsKeep);

produced the following data sample in the log:

data WORK.QUERY_FOR_AVERAGE;
  infile datalines dsd truncover;
  input File_Ref:$72. _TYPE_:32. _FREQ_:32. avgmarkettop5:32.;
  label PremiumRank="Rank for Variable Premium";
datalines;
Input_100 0 5 14.17
;;;;

When pasting this into SAS Studio for testing, all data were written into the first column:

--------------------

There are short articles about how to write data into data step

Jedi SAS Tricks: The DATA to DATA Step Macro - SAS Learning Post

How to create a data step version of your data AKA generate sample dat... - SAS Support Communities

Running the snippet gave warning

*data set you want to create demo data for;
%let dataSetName = sashelp.Class;
*number of observations you want to keep;
%let obsKeep = 5;


******************************************************
DO NOT CHANGE ANYTHING BELOW THIS LINE
******************************************************;

%let source_path = https://gist.githubusercontent.com/statgeek/bcc55940dd825a13b9c8ca40a904cba9/raw/865d2cf18f5150b8e887218dde0fc3951d0ff15b/data2datastep.sas;

filename reprex url "&source_path";
%include reprex;
filename reprex;

option linesize=max;
%data2datastep(dsn=&dataSetName, obs=&obsKeep);

WARNING: Apparent symbolic reference LBLLIST not resolved.

filename tagset url "https://gist.githubusercontent.com/statgeek/7be124b98b37d51e7c002b85b6e9cf72/raw/ec0bc5cf8e1a3386c5cf71d2280b242b7f87dadd/SAS_tagsets_sql_reprex.sas";

%include tagset;

   ods tagsets.sql file="/folders/myfolders/class.txt";
   proc print data=sashelp.class ;
   run;
   ods tagsets.sql close;

Running the above gave errors:

ERROR: Physical file does not exist, E:\folders\myfolders\class.txt.
ERROR: No body file. TAGSETS.SQL output will not be created.

How to create data steps for forum questions?

If sample data has lots of rows, then how to take a portion?

Re: The DATA to DATA Step Macro

PaigeMiller — Thu, 31 Aug 2023 13:30:09 GMT

I'm kind of lost about what you are trying to say or do with a .txt file. I'm also lost about why ODS TAGSETS shows up in your code as it is irrelevant to this using macro.

What you have to do is take an actual SAS data set, not a .txt file or any other file that is not a SAS data set, and then

%data2datastep(dsn=&dataSetName, obs=&obsKeep)

where &datasetname is the name of your SAS data set, let's suppose the data set name is YOUR_DATA_SET_NAME. If you only want the first 27 lines, you set &obskeep equal to 27.

Example

%data2datastep(dsn=your_data_set_name, obs=27)

If the data is not in a SAS data set yet (for example, it is in a text file), then read it into SAS using PROC IMPORT or via a DATA step. Then you can use the %data2datastep macro on this SAS data set.

Re: The DATA to DATA Step Macro

Tom — Thu, 31 Aug 2023 13:33:18 GMT

The first issue is caused by LBLLIST not being defined as one of the LOCAL macro variables.

You can avoid the note by just creating such a macro variable before calling the macro:

%let lbllist=;
%data2datastep(dsn=&dataSetName, obs=&obsKeep);

But that version of the macro also does not seem to generate valid code. Perhaps because the DSD option is not added when the data lines were written? Or because the delmiter is not specified in the generated INFILE statement?

data WORK.CLASS(label='Student Data');
  infile datalines dsd truncover;
  input Name:$8. Sex:$1. Age:32. Height:32. Weight:32.;
datalines;
Alfred M 14 69 112.5
Alice F 13 56.5 84
Barbara F 13 65.3 98
Carol F 14 62.8 102.5
Henry M 14 63.5 102.5
;;;;

Resulting dataset:

The second problem is you did not use a valid path in the ODS statement. From your error message your SAS session is running on Windows and the current drive is set to E: and that drive does not have a directory named folders/myfolders under it. Just use a valid filename in the ODS statement.

If you want to generate code you can post to share your data you could also try %DS2POST() macro.

* Pull macro definition from GITHUB and dump code to the SAS log ;
filename ds2post url
  'https://raw.githubusercontent.com/sasutils/macros/master/ds2post.sas'
;
%include ds2post ;
%ds2post(sashelp.class);

Result

data work.class (label='Student Data');
  infile datalines dsd dlm='|' truncover;
  input Name :$8. Sex :$1. Age Height Weight ;
datalines4;
Alfred|M|14|69|112.5
Alice|F|13|56.5|84
Barbara|F|13|65.3|98
Carol|F|14|62.8|102.5
Henry|M|14|63.5|102.5
James|M|12|57.3|83
Jane|F|12|59.8|84.5
Janet|F|15|62.5|112.5
Jeffrey|M|13|62.5|84
John|M|12|59|99.5
Joyce|F|11|51.3|50.5
Judy|F|14|64.3|90
Louise|F|12|56.3|77
Mary|F|15|66.5|112
Philip|M|16|72|150
Robert|M|12|64.8|128
Ronald|M|15|67|133
Thomas|M|11|57.5|85
William|M|15|66.5|112
;;;;

If you want to post just a subset of the data then you could either make a copy of the subset (or make a view that selects the subset).

data males/view=males;
 set sashelp.class;
 where sex='M';
run;
%ds2post(males)

Results

data work.males ;
  infile datalines dsd dlm='|' truncover;
  input Name :$8. Sex :$1. Age Height Weight ;
datalines4;
Alfred|M|14|69|112.5
Henry|M|14|63.5|102.5
James|M|12|57.3|83
Jeffrey|M|13|62.5|84
John|M|12|59|99.5
Philip|M|16|72|150
Robert|M|12|64.8|128
Ronald|M|15|67|133
Thomas|M|11|57.5|85
William|M|15|66.5|112
;;;;

Re: The DATA to DATA Step Macro

Patrick — Thu, 31 Aug 2023 13:57:20 GMT

I forgot what and why I had to change something small in the original macro but attached the version I'm using plus a sample call that creates data step code for the first 10 rows of table sashelp.class.

%let source_table=sashelp.class;
%let target_file=c:\temp\class.sas;
%let n_obs=10;
%data2datastep(&source_table,,,&target_file,&n_obs);

Re: The DATA to DATA Step Macro

actuarial — Thu, 31 Aug 2023 19:24:31 GMT

Thank you for the attatched script.

Would you be able to let me know what is "source_table" and "target_file", please?

Does the source need to be a text file, or a SAS table?

Re: The DATA to DATA Step Macro

actuarial — Thu, 31 Aug 2023 19:41:22 GMT

This method is great! Thank you so much.

I was able to generate the data step, but I don't follow how to filter to keep a sample of required information.

I got a snippet here:

data work.QUERY_FOR_AVERAGE1_0000 ;
infile datalines dsd dlm='|' truncover;
input File_Ref :$72. _TYPE_ _FREQ_ avg ;
format File_Ref $char72. ;
informat File_Ref $char72. ;
datalines4;
Input_100|0|85|37.2225882352941
Input_101|0|82|51.7714634146341
Input_102|0|64|23.78859375
Input_103|0|82|34.3660975609756
Input_104|0|85|32.2994117647058
Input_105|0|85|36.7142352941176
Input_106|0|82|61.3445121951219
Input_107|0|85|19.6250588235294
Input_108|0|85|28.0389411764705
Input_109|0|67|42.8680597014925
Input_110|0|85|40.6709411764705
Input_112|0|85|23.7874117647058
Input_113|0|85|34.6115294117647
Input_114|0|64|52.91453125
3 The SAS System 12:30 Thursday, August 31, 2023

Input_115|0|84|51.5966666666666
Input_116|0|81|67.6408641975308
Input_117|0|84|25.7155952380952
Input_118|0|84|40.9023809523809
Input_119|0|84|39.5719047619047
Input_30|0|73|95.3902739726027
;;;;

How do I keep, say, the first 5 rows?

Re: The DATA to DATA Step Macro

actuarial — Thu, 31 Aug 2023 19:45:59 GMT

I can manually delete rows.

I don't follow the part

data males/view=males;
set sashelp.class;
where sex='M';
run;
%ds2post(males)

What does "males/view=males" mean?

Re: The DATA to DATA Step Macro

Tom — Thu, 31 Aug 2023 20:09:29 GMT

The /VIEW= option on the DATA statement allows you to create a VIEW instead of a physical dataset. The name after = has to match one of the names listed on the data statement.