topic Re: Wildcard? Macros? Macros with a wildcard? in SAS Procedures

Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Thu, 02 Dec 2021 17:54:57 GMT

Hi,

I have multiple sas transport files (.stc) that I am trying to import first into the sas data files (.sas7bdat). I am trying to use the macro to import all at once. However, my datafiles are named similar to sn55632-00001_blah-qwerty.stc, sn55632-00002_blah-yuiop.stc, sn55632-00003_blah-asdfg.stc, and so on. So, if you see the pattern here, the numbers after the first hypen are increasing by a unit. That's why I found somewhere that I could do 'mypath/sn55632-0000:.stc' (notice the colon there before .stc). However, it's not working for me for some reasons. Could anyone please guide me how I can go about it?

Thank you!

Re: Wildcard? Macros? Macros with a wildcard?

SASKiwi — Thu, 02 Dec 2021 18:57:12 GMT

In SAS the colon is not a true wild card. It allows you to select variables that start with the text to the left of the colon. You can't use it in file names where you need to follow the OS file naming rules. There are other ways of doing what you want but you first need to post a working hard-coded version of your program.

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Thu, 02 Dec 2021 19:23:09 GMT

I am working on this private data on a standalone device so I cannot really share exactly what I am doing. However, I can probably write the same code with different filenames to show here. What would be the relevant wildcard operator instead of colon in SAS as you mentioned?

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Thu, 02 Dec 2021 19:34:38 GMT

option nofmterr;

libname w 'C:\Users\Public\ABCDE\alldata';

%macro import (n);

filename transfile 'C:\Users\Public\ABCDE\alldata\sn55632-0000&n.\:.stc';

proc cimport lib=w infile =tranfile;

run;

%mend import;

%import (1);

This is what I have right now.

The filenames are similar to:

sn55632-00001_blah-qwerty.stc

sn55632-00002_blah-yuiop.stc

sn55632-00003_blah-asdfg.stc

sn55632-00049_blah-asdfg.stc

Re: Wildcard? Macros? Macros with a wildcard?

Tom — Thu, 02 Dec 2021 19:45:53 GMT

Looks like you want to build the list of filename first.

If you can run operating system commands then use that. For example on unix you could use the ls command.

data files;
  infile "ls /users/public/abdcde/alldata/*.stc" pipe truncover;
  input filename $200.;
run;

Or for Window you might use the DIR command.

data files;
  infile "c:; cd C:\Users\Public\ABCDE\alldata; dir /b *.stc" pipe truncover;
  input filename $200.;
run;

Or you might need to use DOPEN() and DREAD() commands instead to get the list of files.

data files;
  rc=filename('dir', 'C:\Users\Public\ABCDE\alldata');
  did=dopen('dir');
  do index=1 to dnum(did);
    length filename $200;
   filename=dread(did,index);
   if 'stc'=scan(filename,-1,'.') then output;
  end;
  rc=dclose(did);
run;

Once you have the list of filenames use that to generate the code to process each STC file.

What type of files are these STC files? The STC extension has no fixed meaning. If you don't know that they are take a look at the first few records in the file.

data _null_;
  infile 'C:\Users\Public\ABCDE\alldata\*.stc' lrecl=80 recfm=f obs=5;
  input;
  list;
run;

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Thu, 02 Dec 2021 20:18:03 GMT

Awesome. Thank you!

The 3rd block of code you provided worked great. I might still need some help looping through those names to import them as .sas7bdat files.

Re: Wildcard? Macros? Macros with a wildcard?

SASKiwi — Thu, 02 Dec 2021 20:41:17 GMT

Use CALL EXECUTE with @Tom 's program:

%macro import (filename = );

proc cimport lib=w infile = "&filename";
run;

%mend import;

data files;
  rc=filename('dir', 'C:\Users\Public\ABCDE\alldata');
  did=dopen('dir');
  do index=1 to dnum(did);
    length filename $200;
   filename=dread(did,index);
   if 'stc'=scan(filename,-1,'.') then 
   call execute('%import(filename =' !! strip(filename) !! ');');
  end;
  rc=dclose(did);
run;

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Fri, 03 Dec 2021 19:47:49 GMT

Thank you @Tom and @SASKiwi. No doubt you all are Super User. I had never utilized the forum before. Well, I guess I am under the shadow of the legends.

Muchas Gracias!

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Fri, 03 Dec 2021 20:31:13 GMT

Further Question: I am using a similar method to sort the data.

%macro sort (filename = ); proc sort data=w.&filename; by ID; run; %mend sort; data files; rc=filename('dir', 'C:\Users\Public\ABCDE\alldata'); did=dopen('dir'); do index=1 to dnum(did); length filename $200; filename=dread(did,index); if 'sas7bdat'=scan(filename,-1,'.') then call execute('%sort(filename =' !! strip(filename) !! ');'); end; rc=dclose(did); run;

The problem with this is that the filename includes '.sas7bdat', and when it goes to proc sort, it'll throw an error because proc sort data=filename needs the filenames without extensions. How do I do it? I tried doing strip(filename-'.sas7bdat') to see if that's a thing but it didn't work either.

Thank you!

Re: Wildcard? Macros? Macros with a wildcard?

Tom — Fri, 03 Dec 2021 21:02:05 GMT

You can either use SCAN() to extract just the member name from the filename.

call execute(cats('%sort(filename=',scan(filename,1,'.'),')'));

Or change the macro to accept a full dataset name instead of just a member name in the W libref.

%macro sort (dataset);

proc sort data=&dataset ;
by ID;
run;

%mend sort;

And then you could pass in either W.MEMBER or just the quoted physical name.

call execute(cats('%sort(',quote(catx('\','C:\Users\Public\ABCDE\alldata',filename)),')'));

But if you already have a libref pointing to the directory there is no need to look for files. Just look for datasets. In fact you should only look for datasets that have the ID variable.

libname w 'C:\Users\Public\ABCDE\alldata';
proc sql;
create table members as
  select libname,memname
  from dictionary.columns
  where libname='W' and upcase(name)='ID'
  order by libname,memname
;
quit;
data _null_;
  set members;
  call execute(catx(' ','proc sort data=',catx('.',libname,memname),';by id;run;'));
run;

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Fri, 03 Dec 2021 21:27:29 GMT

Wonderful. It worked like a charm! And thank you for pointing out the good programming practice out there.

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Fri, 03 Dec 2021 22:09:49 GMT

Is it okay if I ask more questions @Tom ? I feel like I have been learning a lot this way.

So, I have 134 datasets (now SAS datasets, thanks to you), all sorted within the same library. Now I need to merge them in two ways.

First, I need to merge, them into four phases:

1st Phase: s12345_0001_ads_qwerty

....

s12345_0049_ads_cvbnm

2nd Phase: s6789_0001_ads2_dfghk

......

s6789_0028_ads2_zxcvb

and so on.

If you see the pattern here, the first name before the underscore stays the same, while the number after the underscore increases by 1 throughout the phase.

Secondly, I need to merge all 134 of them into one dataset.

The problem with using similar macros, in this case, is I guess you have one filename passed to it at once, so SAS doesn't recognize what to merge. While with the SQL, I have hard time assigning dataname into the table, if that's the way.

Also, I have not used loops to run individual files through the merge statement yet.

Thank you!

Re: Wildcard? Macros? Macros with a wildcard?

Tom — Fri, 03 Dec 2021 23:20:04 GMT

So each dataset has ID and some other set of distinct variables that have different names than the variables in the other similarly named datasets?

data s12345;
  merge w.s12345_: ;
  by id;
run;
data s6789;
  merge w:s6789_:;
  by id;
run;

Are you sure don't want to SET them together instead of MERGEing them?

data s12345;
  set w.s12345_: indsname=indsname;
  by id;
  source=indsname;
run;

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Sat, 04 Dec 2021 00:27:44 GMT

Yes. Each dataset has ID as a unique identifier. There are ~1400 observations altogether i.e. number of participants. 1st phase would have different datasets for different observations for 3 years on those participants.
2nd phase has those observations for another 3years for the same participants. Likewise for the 3rd and 4th phases.
So, if I set them, there will be 4(number of phases)*1400 observations = 6400 observations, while I only want 1400 observations.

Re: Wildcard? Macros? Macros with a wildcard?

Tom — Sat, 04 Dec 2021 03:41:40 GMT

If you have 4 years of data for 1,400 subjects then you SHOULD have 4*1,400 = 6,400 observations.

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Mon, 06 Dec 2021 19:44:24 GMT

Right. However, it's the data for the same participants over four years. So, based on my understanding, I will have more variables/columns for the same 1400 participants. Please correct me if I am wrong, or suggest to me what you think is efficient.
Thank you!

Re: Wildcard? Macros? Macros with a wildcard?

Tom — Mon, 06 Dec 2021 20:22:40 GMT

We cannot tell without seeing how your study is setup.

Normally if you are asking the same questions the values should be stored into the same variable. If the question is different then it will require a different variable. For example if you ask their weight in the first wave/year and again in the follow-up years then you should store that into the same variable and have different observations for the separate wave/year. But if you asked their change from baseline weight that would be a different variable and only apply to the the follow-up waves and not the initial wave. So the variable will have a missing value for the observation that represents the wave where it does not apply.

Re: Wildcard? Macros? Macros with a wildcard?

Sudeep_Neupane — Mon, 06 Dec 2021 20:36:53 GMT

Makes sense. It seems like I will have to SET then as it matches your first scenario. Well basically, I have four waves, and within those waves, the same variable is calculated 5 to 6 times at different periods of time.

Re: Wildcard? Macros? Macros with a wildcard?

Tom — Mon, 06 Dec 2021 20:43:22 GMT

@Sudeep_Neupane wrote:
Makes sense. It seems like I will have to SET then as it matches your first scenario. Well basically, I have four waves, and within those waves, the same variable is calculated 5 to 6 times at different periods of time.

Depending on how it is stored you might have a series of variables. X1 - X6 for example. Or you might have multiple observations per wave with the time period as a secondary key variables. But that is probably not how it is currently setup based on the observation counts you provided before.

You can always convert from one structure to another structure depending on your reporting/analysis needs.