topic Re: macro into a table and a macro variable with sas in SAS Procedures

macro into a table and a macro variable with sas

andy_wk — Fri, 20 Jun 2014 15:00:41 GMT

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.

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

PaigeMiller — Fri, 20 Jun 2014 15:23:29 GMT

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?

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

andy_wk — Fri, 20 Jun 2014 15:27:01 GMT

Hi Paige,

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

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

andy_wk — Fri, 20 Jun 2014 15:28:11 GMT

What is the difference between single quote and double quote?

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

PaigeMiller — Fri, 20 Jun 2014 15:39:18 GMT

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.

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

andy_wk — Fri, 20 Jun 2014 15:50:04 GMT

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.

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

PaigeMiller — Fri, 20 Jun 2014 16:25:21 GMT

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

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

Tom — Fri, 20 Jun 2014 16:58:20 GMT

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;