01-13-2017 06:37 AM - edited 01-13-2017 06:39 AM
Hello SAS users,
I have 36 individual datasets (one dataset per month for 3 years) titled "monthyear" (eg. "12012" for Jan 2012). I want to open then up, sort them, create a new variable and close them up. Preferebly, in an automated fashion.
In STATA, I would be able to write a loop for it. I would start the loop BEFORE opening up the dataset, and have it run through values i=1/12, j=1/3.
In SAS this would look something like this:
do i= 1 to 12;
do j= 1/3;
However, in SAS the DO statement must be preceded by the DATA statement that specifies the dataset. Therefore, this won't run.
Is there a solution for this?
01-13-2017 06:46 AM
Well, to start with 12012 is not a valid name for a dataset, so your datasets aren't called that. Also, is the year always 4 digits? It would be a good idea to have some sort of standardisation to make finding month easier, is it 1 digit or 2?
Now as for looping, thats pretty simple, you have the metadata information in sashelp.vtable (and vcolumns for columns) and from that you can simply create your code:
data _null_; set sashelp.vtable (where=(libname="<yourlib>" and substr(memname,1,4)="DTA1")); call execute('data '||catx('.',libname,memname)||'; set '||catx('.',libname,memname)||'; do some code here; run;'); run;
What this does is open the SAS metadata, and take only rows with given libname and prefix of DTA1 (as your given names are incorrect). Then for each of these datasets it will generate a datastep with the code.
Here is an example:
data work.dta1_012012; set sashelp.class; run; data work.dta1_022012; set sashelp.class; run; data _null_; set sashelp.vtable (where=(libname="WORK" and substr(memname,1,4)="DTA1")); call execute('data '||catx('.',libname,memname)||'; set '||catx('.',libname,memname)||'; myflag=1; run;'); run;
01-13-2017 07:07 AM
So if I understand correctly, I have to open up each individual dataset and create a work.xxx dataset out of them.
If that's the case, I might as well do the entire thing with copy/paste and run the code.
But thanks for the response.
01-13-2017 07:18 AM
No, this code will generate the necessary code to perform the same datastep on every dataset name returned by the where clause. Try running the code and you will see what I mean. You do not have to copy/paste or anything else, just run that code.
01-13-2017 06:58 AM
Are you sure on the existing data set names like 12012, 21012 ..and so on.
Data set name should start with either '_' or a 'character' . Clarify on the existing source data.
01-13-2017 07:01 AM
And while we're at defining names for datasets, the sequence in the filename should be year - month instead of month - year. Much easier for handling in the future.
01-13-2017 08:08 AM
Try this way ..
/**listing the data from a particular library where the actual data resides and by giving proper condition**/
create table listData as
where libname="&libname" and memname like "&prefix%" ;/*here prefix or suffix how you are identifying data set*/
/**Count of total no.of data sets **/
select count(*) into :cnt from listData;
/**create macro variables for each data set..lets say in your case it is 36**/
/**like memname1=12012 memname2=22012......memname36=122014**/
%do i=1 to &cnt.;
select memname into :memname&i.-memname&cnt. from listData;
/** calling one by one using do loop and create the required new varibles**/
%do i=1 to &cnt.;
&newvar=;/*apply logic here if this is common for all*/
/*invoke the macro by giving your library name and prefix or suffix*/
01-13-2017 09:08 AM
Here's a macro I believe will do the cycle for you.
%macro doit(PREFIX,NAME,SUFIX); * enable extended data names support; options VALIDMEMNAME=EXTEND; %do _P=1 %to PREFIX; %do _S=1 %to SUFIX; * create new var; data "&_P&NAME&_S"n; set "&_P&NAME&_S"n; <new_var>=<some_value> run; * sort the data by desired var; proc sort; by <desired_var>; run; %end; %end; %mend doit; %doit(12,201,3); * do it;
As said previously, data set naming explicitly forbids the use of a number at first position.
For that you must enable extended name support with the VALIDMEMNAME option. Then you can name you data whatever you want if enclosed in '<data name>'n or "<data name>"n.
More on names in SAS here: http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p18cdcs4v5wd2dn1q0x2...
Hope it helps.
Daniel Santos @ www.cgd.pt
Need further help from the community? Please ask a new question.