Hello,
I have a sas work dataset as below ( only one column)
ID
12
34
56
78
I need to assign these ID's again into a single variable as like below
%let = "12","34","56","78";
I tried with catx funtion to convert multiple rows into single row but didnot help. Please help me to achieve this.
The simplest way to make a macro variable from data is using PROC SQL and in the INTO keyword.
So to make a macro variable named LIST from the values of ID in the dataset HAVE you could use something like this:
proc sql noprint;
select distinct quote(trim(ID),"'") into :list separated by ','
from have
;
quit;
What to you plan to DO with the macro variable? Do you really need the commas? The IN operator in SAS does not mind if you use spaces instead of commas between the items in the list. Spaces in macro variables are much easier to work with than commas.
Why do you need this? What is the benefit? What will you do next once you have this variable?
By the way, are you talking about a MACRO variable? You didn't actually say that, but you use %LET.
Is the value of ID duplicated in the data set?
If so should there be two or more copies in the data set?
Does the order of the values have to match the order they appear in the data set?
Is the variable numeric or character?
How many values are we talking about? There is a limit on the number of characters in a macro variable and adding basically 3 characters for each value means you start cutting into the number of values that will even fit.
Almost every time we see a "place all the values of a variable in a data set into a macro variable" we see a follow up with attempts to use that variable in a manner that is very inefficient, cumbersome or just plain fails. So a clear description of what is to be done with this may be more helpful.
The simplest way to make a macro variable from data is using PROC SQL and in the INTO keyword.
So to make a macro variable named LIST from the values of ID in the dataset HAVE you could use something like this:
proc sql noprint;
select distinct quote(trim(ID),"'") into :list separated by ','
from have
;
quit;
What to you plan to DO with the macro variable? Do you really need the commas? The IN operator in SAS does not mind if you use spaces instead of commas between the items in the list. Spaces in macro variables are much easier to work with than commas.
Thanks All for the suggestion. didnot realise proc sql has an easy way out.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: