Creating a new dataset with some variables from many other datasets

[2222]

Hi all,

Im trying to create a dataset that takes two variables (A and t3) from a dataset called E99. every E99 is stored in a folder path - \\p\d\6\L&qtr&yr\p\d\a (where the &qtr is Mar, Jun, Sep or Dec and the yr is the two-digit year reference from 2000 (00) to 2022 (22)).

i only want the rows from e99 that have 44 in the A variable. and the new dataset should say what the date is that the rest of the row's data comes from.

so far i have this (but it might be so far off that it isn't even a start):

%macro create_new_dataset(qtr, yr, libname);
%local i j dsname;
%let dsname = new_dataset;

data &dsname;
set
%do i = 1 %to %sysfunc(countw(&qtr));
%do j = 1 %to %sysfunc(countw(&yr));
&libname.\L%scan(&qtr,&i)%scan(&yr,&j)\p\d\a\e99.sas7bdat (where=(A=2544) keep= A t3);
%end;
%end;
;
run;

%mend create_new_dataset;

%create_new_dataset(Mar Jun Sep Dec, 18 19 20 21 22, \\s\d\6);

also this code might over write the qtrs and id only get Dec22, so can you please help me have it make the new dataset include all the qtrs from all the years of interest. (i also think having a %do 00 %to 22 would be better than what i have for specifying the years of interest, but not sure how to do that either)

[ballardw]

Show the code that you had to do this before introducing the Macro coding.

If you don't have such, then get code to work without macros before attempting to write the macro. 

Hint: if data sets are stored in a "folder path" assign a library using an actual LIBNAME statemen to that path before doing anything and use libref.datasetname. Write one macro to create the libraries. Then when that works a separate one to use them.

[2222]

thank you, that is the whole program, there is no other code. for the libname, im not really sure how to do that, can you help, because the datasets i want to take from are all in slightly different locations. each comes from a different qtr/year folder, so would i need a macro with the libname in it or something?

[AlanC]

Libnames can have multiple entries: LIBNAME TEST ("path1","path2");

[2222]

but there are 23000 datasets, i can write them all out. i was thinking that since each dataset in in a folder whos name is only different in the qtr and yr we could read them all with a macro and just 1 libname statement

[AlanC]

You aren't worried about the number of datasets but the number of directories. A libname only has paths, not the individual files.

[AlanC]

I don't have SAS or I could write up some code for you. Let me tell you how I would approach it so it is easier to debug, IMO. Since I don't have SAS, I am winging it but it should provide an idea. Personally, I hate macros due to their obscurity. Just run a data step and %include back in. Easy to see if your code is valid. 

data _null_;

     file "c:\temp\MyDataRead.sas" ;

     input sourceData;

     input month

              year

              ;

     if _n_ = 1 then put "libname myLibs";

    put "myLibs" month +(-1)"." year;

run;

%include "c:\temp\MyDataRead.sas";

[2222]

thank you, will this put the datasets in the program (i dont want that) just want to read the datasets so i can take some variable values from each and put them all in one dataset. also the datasets are all in a slightly different folder as i have put. the folder path is s\d\3\I&qtr.&yr\p\d\a the only thing that changes is the qtr (to be one of Mar Jun Sep Dec and the year to be between 2000 (or00) and 2022 (22)

[AlanC]

No, what it will do is what is known as 'codegen'. Generate the SAS code, see if it matches what you expect, then %include it once you know it generated it properly. This is kind of how the macro engine operates but by pushing the code to a text file, you can see what it is doing. Keep refining it until it looks like it will execute as expected, then %include it in. 

It mainly gives you a simple way to debug plain SAS code w/o worrying about macro stuff. Use standard do loop concepts. See my Tips and Tricks on my github site for an example:

savian-net/SasTipsTricks: Tips and tricks learned over 20+ years as a SAS/Microsoft consultant (gith...

Look at PUTs vs Macros. You may not want to do it this way. I have found that it is far easier to debug than macros.