DATA Step, Macro, Functions and more

PROC SQL Insert DO LOOP with a Macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

PROC SQL Insert DO LOOP with a Macro

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.


Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 1,299

Re: PROC SQL Insert DO LOOP with a Macro

Posted in reply to Puertorekinsam

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);

View solution in original post


All Replies
Respected Advisor
Posts: 2,647

Re: PROC SQL Insert DO LOOP with a Macro

Posted in reply to Puertorekinsam

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.

--
Paige Miller
Solution
3 weeks ago
PROC Star
Posts: 1,299

Re: PROC SQL Insert DO LOOP with a Macro

Posted in reply to Puertorekinsam

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);
Occasional Contributor
Posts: 5

Re: PROC SQL Insert DO LOOP with a Macro

Posted in reply to novinosrin

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!

Super User
Posts: 6,536

Re: PROC SQL Insert DO LOOP with a Macro

Posted in reply to Puertorekinsam

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;

end;

stop;

drop k;

run;

Occasional Contributor
Posts: 5

Re: PROC SQL Insert DO LOOP with a Macro

Posted in reply to Astounding

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;
Super User
Posts: 13,016

Re: PROC SQL Insert DO LOOP with a Macro

Posted in reply to Puertorekinsam

Or

 

%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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 105 views
  • 0 likes
  • 5 in conversation