macro into a table and a macro variable with sas

[andy_wk]

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.

[PaigeMiller]

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?

[andy_wk]

Hi Paige,

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

[andy_wk]

What is the difference between single quote and double quote?

[PaigeMiller]

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.

[andy_wk]

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.

[PaigeMiller]

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

[Tom]

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;