topic Re: How do I create a dataset from a dynamic set statment? in SAS Enterprise Guide

How do I create a dataset from a dynamic set statment?

JGx — Thu, 03 Oct 2019 17:59:44 GMT

EG 7.13

I want to generate a single data set from many. Each dataset has the same format, structure. To do this I envision a data statement that calls a macro which sets my data from a concatenated files list using a date range. Like this:

/*CREATE FILE LIST*/
%MACRO file_part (startdate=, enddate=);

DATA file_list (DROP=date);
   DO date= "&startdate"d TO"&enddate"d;
   FORMAT date yymmddn8.;
   file_part = cats("/mydir/",put(date,yymmddn8.),"_report");
      OUTPUT;
   END;
   
RUN;
%MEND;

/*Line of code for testing file_part as standalone*/
/*%FILE_PART(startdate=01Jan2019, enddate=1Oct2019);*/

/*GET MY DATA*/
DATA mydata;
SET %file_part(startdate=01Jan2019, enddate=10Oct2019);
RUN;

ERROR: File WORK.DATA.DATA does not exist.
ERROR: The variable date in the DROP, KEEP, or RENAME list has never been referenced.

My research leads me to believe the DO statement in file_part is in correct, but I can't pintpoint why. How can I fix the code above to return the expected results (one dataset set from many)? Errors in code block.

Re: How do I create a dataset from a dynamic set statment?

Reeza — Thu, 03 Oct 2019 15:09:17 GMT

It looks like you're reading from a text file. Do you need to limit the dates or do you need to read all files.

SAS allows wildcards/filelists so reading multiple files at once is trivial.

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

FYI - you cannot use SET with text files, you need to first convert them to a SAS data set format.

@JGx wrote:

EG 7.13

I want to generate a single data set from many. Each dataset has the same format, structure. To do this I envision a data statement that calls a macro which sets my data from a concatenated files list using a date range. Like this:
/*CREATE FILE LIST*/
%MACRO file_part (startdate=, enddate=);

DATA file_list (DROP=date);
   DO date= "&startdate"d TO"&enddate"d;
   FORMAT date yymmddn8.;
   file_part = cats("/mydir/",put(date,yymmddn8.),"_report.txt");
      OUTPUT;
   END;
   
RUN;
%MEND;

/*Line of code for testing file_part as standalone*/
/*%FILE_PART(startdate=01Jan2019, enddate=1Oct2019);*/

/*GET MY DATA*/
DATA mydata;
SET %file_part(startdate=01Jan2019, enddate=10Oct2019);
RUN;

ERROR: File WORK.DATA.DATA does not exist.
ERROR: The variable date in the DROP, KEEP, or RENAME list has never been referenced.
My research leads me to believe the DO statement in file_part is in correct, but I can't pintpoint why. How can I fix the code above to return the expected results (one dataset set from many)? Errors in code block.

Re: How do I create a dataset from a dynamic set statment?

JGx — Thu, 03 Oct 2019 18:01:53 GMT

Thanks Reeza,

Actually, no, the files are all SAS datasets. I removed '.txt' from the OP.

Yes, I need to maintain integrity of the data range within the macro parameters.

Re: How do I create a dataset from a dynamic set statment?

Tom — Thu, 03 Oct 2019 18:33:13 GMT

When the macro processor runs the macro it passes the generate text back to SAS to run.

So your are trying to run:

SET DATA file_list (DROP=date);

Which is why you get the message about WORK.DATA not existing. You must have previously run the macro and generated the dataset named FILE_LIST, but since your didn't keep the variable DATE you are getting an error when trying to drop it on the way into this data step.

Just pull the list of names out the dataset. How many names is it going to be? Will the whole list fit into a single macro variable (maximum of 65K bytes)?

%FILE_PART(startdate=01Jan2019, enddate=1Oct2019);

proc sql noprint;
  select quote(trim(file_part)) into :dslist separated by ' '
  from file_part
  ;
quit;

/*GET MY DATA*/
DATA mydata;
  SET &dslist ;
RUN;

If not then modify the macro to only generate the list of names instead of generating SAS code.

%MACRO file_part (startdate=, enddate=);
%local date ;
%do date= %sysevalf("&startdate"d) %to %sysevalf("&enddate"d);
   "/mydir/%sysfunc(putn(&date,yymmddn8.))_report"
%end;
%MEND;

Then you can use it in the middle of another statement if you want.

DATA mydata;
  SET %file_part(startdate=01Jan2019, enddate=10Oct2019);
RUN;

Note: Do you really want combine 283 separate datasets?

Re: How do I create a dataset from a dynamic set statment?

JGx — Fri, 04 Oct 2019 20:01:03 GMT

Thanks, this will do the trick. Yes, batching from 283 to 1, then appending singles everyday after with chron.