BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Puertorekinsam
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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
novinosrin
Tourmaline | Level 20

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);
Puertorekinsam
Fluorite | Level 6

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!

Astounding
PROC Star

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;

Puertorekinsam
Fluorite | Level 6

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;
ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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