topic Re: PROC SQL Insert DO LOOP with a Macro in SAS Programming

PROC SQL Insert DO LOOP with a Macro

Puertorekinsam — Thu, 03 May 2018 16:50:24 GMT

Hello,

I am trying to prepopulate a table with some values to make sure all combinations are accounted for in the final data set .

I tested a simple PROC SQL insert step:

PROC SQL;
 
INSERT INTO work.Temp
	Set site= 'WBK'
	,DurationCat = put(1+( 1*.25),5.2)
	,DurationCount=0
	;quit;

to make sure I had the right syntax for the insert, and it worked as expected.

Now what I want is to pass the Site name in through a Macro, and then do a loop where the 1*.25 is actually {0*.25,1*.25... ...10*.25,11*.25}

this way I get an entry for each site with a duration category of 1 to 3.75 moving by 0.25

So I modify the code to a macro :

%Macro StartTable (whichsite);
 PROC SQL;
 %do k = 0 %to 11;
INSERT INTO work.Temp
	Set site= &whichsite.
	,DurationCat = put(1+( &k.*.25),5.2)
	,DurationCount=0
%END
	;quit;
%mend StartTable;

which I call with

%StartTable(whichsite='WBK');
%StartTable(whichsite='WCK');
%StartTable(whichsite='WDK');

However I am getting this error:

ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, /, SET, ||.

I am sure I am doing something dumb and missing a simple syntax error, but for the life of me I can't figure it out. Any help would be appreciated.

Re: PROC SQL Insert DO LOOP with a Macro

PaigeMiller — Thu, 03 May 2018 16:53:41 GMT

A couple of issues. If you show us the SASLOG, we might better be able to determine where/what the error is. Just showing us the error message doesn't help.

Next, if you are having problems with macros, and you have already gotten the code to work without macros, then you need to use the command

options mprint;

before you run your code, this will cause SAS to show you the actual code used by resolving the macro, and this actual code used will appear in the SASLOG. At that point, you ought to be able to pick out the error yourself.

Re: PROC SQL Insert DO LOOP with a Macro

novinosrin — Thu, 03 May 2018 17:05:19 GMT

see if this works?

%Macro StartTable (whichsite);
 PROC SQL;
 %do k = 0 %to 11;
INSERT INTO work.Temp
	Set site= "&whichsite."
	,DurationCat = put(1+( &k.*.25),5.2)
	,DurationCount=0
%END
	;quit;
%mend StartTable;

%StartTable(whichsite=WBK);

Re: PROC SQL Insert DO LOOP with a Macro

Astounding — Thu, 03 May 2018 17:16:15 GMT

Is somebody forcing you to use SQL? This is a really easy task for a DATA step, along the lines of:

data want;

DurationCount=0;

do Site='WBK', 'WCK', 'WDK';

do k=1 to 11;

DurationCat = put(1 + (k * 0.25), 5.2);

output;

end;

stop;

drop k;

run;

Re: PROC SQL Insert DO LOOP with a Macro

Puertorekinsam — Thu, 03 May 2018 17:29:31 GMT

No, it's more of a "the devil you know" type situation.

I bounce around from environment to environment based on my needs are (SAS... [R]...Python... Business Objects), I usually do a lot with SQL so I can transfer from one to the other.

Also, I have a lot more sites then the three listed, sometimes they are fed by a user prompt, so I am not limited to just those three, I just gave those as an example because I have seen comments on these boards before of " you're not calling the Macro". Since I knew that wasn't the issue, I wanted to make sure I accounted for it.

I did solve the issue thanks to PaigeMiller's comment about options mprint; I was missing a semicolon (and I put the %DO loop back around the SQL and not in the code)

 %macro StartTable(whichsite);
%do k = 0 %to 11;
PROC SQL;
INSERT INTO work.Temp
	Set site= &whichsite
	,DurationCat = put(1+( &k*.25),5.2)
	,DurationCount=0
;quit;
%END;

%mend StartTable;

Re: PROC SQL Insert DO LOOP with a Macro

Puertorekinsam — Thu, 03 May 2018 17:30:26 GMT

Thanks! I came to the same solution once I turned on the mprint options... The things I don't know since I have no formal training in this stuff...

Thanks everyone!

Re: PROC SQL Insert DO LOOP with a Macro

ballardw — Thu, 03 May 2018 17:31:10 GMT

%Macro StartTable (whichsite);
 PROC SQL;
 %do k = 0 %to 11;
INSERT INTO work.Temp
	Set site= &whichsite.
	,DurationCat = put(1+( &k.*.25),5.2)
	,DurationCount=0
   ;    /*; to end each insert statement*/
%END
	quit;
%mend StartTable;