topic How do I load data using macro loop in New SAS User

How do I load data using macro loop

d0021 — Wed, 01 May 2019 15:00:01 GMT

I have files on a server in the following naming convention.

prefixtest.123.YYYYMM
prefixtest.223.YYYYMM
...

There are many files on the server, so I would like to read them in by sample. I am using SAS 9.4.

Macro runparallel: loops through SampleCodeLst and SampleLst to get the number of samples we have. This macro then calls macro load_data.

Macro load_data: tries to set data to "&prefixtest.&samplecode.." It uses a semi colon to generalize all the monthly file suffixes. It also specifies the variable via macro variable vlist.

%let iLoc =InputFileLocation;
%let oLoc =OutputFileLocation;
%let SampleCodeLst = 123 223; 
%let SampleLst = Sample1 Sample2; 
%let vLst = AccountIdentifier Period ImportantVar;
%let fileprefix = prefixtest; 

%syslput iLoc=%bquote(&iLoc.);
%syslput oLoc=%bquote(&oLoc.);
%syslput vLst=%bquote(&vLst.);
%syslput fileprefix=%bquote(&fileprefix.);

%macro load_data(SampleCode=, Sample=,gsession=);

	%syslput SampleCode=%bquote(&SampleCode.);
	%syslput Sample=%bquote(&Sample.);

	signon &gsession.;
	%put SESSION &gsession. STARTING;

	rsubmit gsession connectwait=no connectpersist=no sysrputsync=yes;
		libname iLoc "&iLoc." access=readonly; run;
		libname oLoc "&oLoc." compress=binary; run;

		data oLoc.&Sample.;
			set &fileprefix.&SampleCode.:(keep= &vLst.)
		run;

endrsubmit;
%mend load_data;

%macro runparallel;
	waitfor _all_
	%do Loop=1 %to %sysfunc(countw(&SampleLst, ' '));
		%Let SampleCode = %sysfunc(scan(&SampleCodeLst,&Loop.,' '));
		%Let Sample = %sysfunc(scan(&SampleLst,&Loop.,' '));
		%load_data(SampleCode=&SampleCode., Sample=&Sample.,gsession=g1_&Loop.);
	%end;
	;
%mend runparallel;
%runparallel;

Two Issues occurs:

1) Run parallel macro: Looping may or may not be an issue. I'm not sure why load_data is not called twice in my scenario. It might be the way I am passing macro variables.

2) load data macro: The "set" data line does not appear to be working at all. See below.

NOTE: Line generated by the macro variable "SAMPLECODE".
prefixtest_123
ERROR: File prefixtest.DATA does not exist....

Re: How do I load data using macro loop

Kurt_Bremser — Wed, 01 May 2019 15:11:07 GMT

Blanks are the default delimiters for countw and scan functions, so they need not (and in your case must not) be specified.

%do Loop=1 %to %sysfunc(countw(&SampleLst));
	%Let SampleCode = %sysfunc(scan(&SampleCodeLst,&Loop.));
	%Let Sample = %sysfunc(scan(&SampleLst,&Loop.));

Re: How do I load data using macro loop

ballardw — Wed, 01 May 2019 15:12:02 GMT

You say that your files have this naming convention:

prefixtest.123.YYYYMM
prefixtest.223.YYYYMM

I do not see anything in our code that attempts to add a YYYYMM portion.

The message

NOTE: Line generated by the macro variable "SAMPLECODE".
prefixtest_123
ERROR: File prefixtest.DATA does not exist....

Indicates that you generated prefixtest_123 not prefixtest.123 that you say are the file names in addition to not having the YYYYMM bit.

Re: How do I load data using macro loop

Tom — Wed, 01 May 2019 16:31:38 GMT

The order of these statements seems backwards. How can you push a value to a macro variable in a remote session you haven't started yet?

	%syslput SampleCode=%bquote(&SampleCode.);
	%syslput Sample=%bquote(&Sample.);

	signon &gsession.;

Personally I have had issues with the new %SYSLPUT macro statement and have frequently gone back to using the old %SYSLPUT() macro that SAS used to supply.

Here is link to a version in GITHUB, Name is changed to %SYSLPUT612() , since you cannot make a macro with the same name as a macro function.

https://github.com/sasutils/macros/blob/master/syslput612.sas