Using PROC SQL "into;" , then using SYMPUT with a long macrovariable

[rmacarthur]

Hi SAS Friends, 

Need help bringing a long complex macrovariable into a SAS dataset.

Attached is a file with 2 columns. 

One column is an index (NO_) and the other (Cit_S)  is a variable containing names and years separated by ",". 

Cit_S variables need to be brought together into a single long variable, each separated by a ";", and then that brought into a SAS dataset. 

I have been using Proc SQL with an "into;" statement, which works very well to create a single macrovariable. 

However am stuck on how to bring the marcrovariable back into SAS. 

When I use SYMPUT within a datastep, to import macrovariable "&Cit_S_string", there are multiple errors related to the variable length and the presence of semicolons, .....that am stuck on.  

Would appreciate your help with this,

Attached is a sample dataset and the PROC SQL code

Thank you,  

R

proc sql;	
	select Cit_S	 
	into:Cit_S_string separated by ";" from sf.test;	
	quit;
%put Cit_S_string	= "&Cit_S_string";

[Stu_SAS · Accepted solution]

Hey @rmacarthur! You do not need to use symput to bring a macro variable into a SAS dataset. Symput creates macro variables from the DATA Step, kind of like the SQL into statement. A macro variable can be thought of as just a reference to text. You can resolve that reference by putting an & in front of the variable name. For example:

data foo;
    string = "&Cit_S_string";
run;

Can you post your DATA Step code as well?

[rmacarthur]

Hi Stu_SAS, 

Thank you, much appreciated that works well for what we're doing.

I've uploaded the datastep, it was based upon adding the new macrovariable to a simple table, which I"ve uploaded, too.

But that is not necessary given this nice solution you've provided.

Thanks again !

Robert

data	sf.cit_T_1	;	set	sf.cit	;	
length	T_List	$5000.	;	
call symputx("T_List", &Cit_S_string);
run;

[Tom]

---

@rmacarthur wrote: ...

data	sf.cit_T_1	;	set	sf.cit	;	
length	T_List	$5000.	;	
call symputx("T_List", &Cit_S_string);
run;

---

That explains why you got errors.  Not sure what text was put into CIT_S_STRING but you did use ; as the delimiter so the value is going to look something like:

aaa;bbb;ccc

So if you replace the reference to the macro variable with the text it contains you end up with a statement like:

call symputx("T_List", aaa;bbb;ccc);

Which is not valid syntax.

But the whole data step is not very useful.  Even if you fix the code so that it passes an actual string to the CALL SYMPUTX() function the step is not really doing anything useful.  It will make sf.cit_T_1 as an exact copy of sf.cit only with the addition of a empty variable with length of 5,000 bytes.  (Since you do not modify any of the variables in the data step.)  The CALL SYMPUTX() function will execute once for each observation read in.  But the value it writes to the macro variable T_LIST will always be the same since it does not depend on any variable in the data step.

To set a value to a variable in a data step use an assignment statement.  You could use the macro processor to generate a string literal in the assignment statement by adding double quote characters around the macro variable reference.

T_List = "&Cit_S_string" ;

That might have trouble if the value CIT_S_STRING includes double quote characters already.  So it would be safer to use the SYMGET() function instead.

T_List = symget('Cit_S_string') ;

Also note that there is no need to include a period when specifying the length of the T_LIST variable.  Lengths of SAS variables are always integers, so there is no need for the decimal point.  Perhaps you are confusing the $nnn using in the LENGTH statement with a format or informat specification?  In SAS code you need to include a period in a format/informat specification so the SAS compiler can distinguish between a variable name reference and format or informat specification.  There is no need to worry about that in a LENGTH statement as you cannot include format or informat specifications there, just variable names and lengths.

And if the goal is a dataset variable and not a macro variable then skip the macro variable generation and just make the dataset variable directly.

data t_list;
  length t_list $5000 ;
  retain t_list;
  set sf.test end=eof;
  t_list = catx(';',t_list,cit_s);
  if eof;
run;

Now you can easily add that variable to your other dataset.

data sf.cit_T_1;
  set sf.cit ;
  if _n_=1 then set t_list;
run;

PS The reason your posted code looks so strange is you appear to have typed TAB characters instead of spaces into the code.

[rmacarthur]

Hi Tom,

Thanks, I learned allot from your summary.

and yes I do type <tab> in my SAS code !

Helps things line up better, which helps me catch mistakes

and look for consistency.

Very much appreciated, 

R.

[yabwon]

I would go with:

data foo;
    string = symget("Cit_S_string");
run;

🙂

Bart

[rmacarthur]

Yes, absolutely !