Help using Base SAS procedures

macro into a table and a macro variable with sas

Reply
Frequent Contributor
Posts: 75

macro into a table and a macro variable with sas

I'm having this macro. The aim is to take the name of variables from the table dicofr and put the rows inside into variable name using a symput.

However , something is not working correctly because that variable, &nvarname, is not seen as a variable.

This is the content of dico&&pays&l

varname descr

var12 aza

var55 ghj

var74 mcy

This is the content of dico&&pays&l..1

varname

var12

var55

var74

Below is my code

%macro testmac;  %let pays1=FR ;  %do l=1 %to 1 ; 

data dico&&pays&l..1 ;

set dico&&pays&l (keep=varname); call symput("nvarname",trim(left(_n_))) ;

run ; 


data a&&pays&l; set a&&pays&l;

nouv_date=mdy(substr(date,6,2),01,substr(date,1,4));

format nouv_date monyy5.;

run;  


proc sql; create table toto (nouv_date date , nomvar varchar (12)); quit; 

proc sql;  insert into toto SELECT max(nouv_date),'&nvarname' as nouv_date as varname FROM a&&pays&l WHERE (&nvarname ne .);  


%end;

%mend; 

%testmac;

A subsidiary question. Is it possible to have the varname and the date related to that varname into a macro variable? My man-a told me about this but I have never done that before.

Thanks in advance.

Trusted Advisor
Posts: 1,612

Re: macro into a table and a macro variable with sas

proc sql;  insert into toto SELECT max(nouv_date),'&nvarname' as nouv_date as varname FROM a&&pays&l WHERE (&nvarname ne .);   

You need to have double-quotes around &nvarname, not single quotes

But there seems to be an additional problem, the way you have coded the call symput, &nvarname will always have the same value ... is that what you want?

Frequent Contributor
Posts: 75

Re: macro into a table and a macro variable with sas

Hi Paige,

I want to have the list of the variables var12  , var55 , var74.


Frequent Contributor
Posts: 75

Re: macro into a table and a macro variable with sas

What is the difference between single quote and double quote?

Trusted Advisor
Posts: 1,612

Re: macro into a table and a macro variable with sas

Macros inside single quotes are treated as plain text, with no macro meaning whatsoever. Thus, '&nvarname' is treated as the text inside the single quotes.

Macros inside double quotes are treated as macros and resolved to their value. Thus, if &nvarname takes on the value 12, then "&nvarname" will be replaced by "12".

To get different values of your macro from the dataset you want something like

call symputx('nvarname'||left(_n_),varname);

Which will give you &nvarname1 to be var12, &nvarname2 to be var55 and so on.

Frequent Contributor
Posts: 75

Re: macro into a table and a macro variable with sas

Paige,

I've replaced the code as such

data dico&&pays&l..1 ;

set dico&&pays&l (keep=varname);

call symputx('nvarname'||left(_n_),varname);

run ; 

proc sql; 

insert into toto SELECT max(nouv_date),"&nvarname" FROM a&&pays&l.._1 WHERE (&nvarname ne .);

And I got this error (see below)

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant,

              a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

Trusted Advisor
Posts: 1,612

Re: macro into a table and a macro variable with sas

well, here its not really clear what you are doing or what you would like to insert into toto <== this is the key question, if I know what you wanted to have here, I think the steps would be simple to advise

but the syntax problem is that &nvarname doesn't exist, its not a macro variable any more, you now have macro variables &nvarname1 and &nvarname2 and &nvarname3

Super User
Super User
Posts: 6,499

Re: macro into a table and a macro variable with sas

What are you really trying to do?

Why not just summarize the data using PROC SUMMARY?

Include a simple example of INPUT and then OUTPUT wanted. Just include a few variables.

So if one table has a list of variable names and you want to use those names in a later place in SAS then perhaps PROC SQL is what you want to use?

proc sql noprint ;

select varname into :varlist separated by ' '  from namelist ;

quit;

proc summary data=HAVE ;

var &namelist ;

output out= WANT max= ;

run;

Ask a Question
Discussion stats
  • 7 replies
  • 313 views
  • 0 likes
  • 3 in conversation