Hi All,
I have following dataset named Month with single variable Mon contains all the names of month.
I need output as follows
'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'
(Each Month separated by ',' )
COULD YOU PLEASE HELP ME.
Data Month;
input Mon $3.;
datalines;
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
;
run;
Data Month;
input Mon $3.;
datalines;
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
;
run;
proc sql noprint;
select quote(mon) into : mons separated by ',' from month;
quit;
%put &mons ;
Hi @anjicamsas
Depending on the situation, you might want to
proc sql noprint;
select quote(strip(Mon),"'")
/* Put the list into a macrovariable*/
into: list_month separated by ", "
from Month;
quit;
%put &list_month.;
data want;
set month end=eof;
length list_month $ 200;
retain list_month;
if _n_=1 then list_month = quote(strip(Mon),"'");
else list_month = catx(", ", list_month, quote(strip(Mon),"'"));
if eof then do;
/* Put the list into a macrovariable*/
call symputx("_list_month",list_month);
/* Output the list in the dataset */
output;
end;
drop mon;
run;
%put &_list_month.;
By default, the QUOTE() function put double quotes. You can specify that you want single quote by adding a second argument: quote(your_variable,"'").
Best,
The core idea is to use CATX with a RETAINed variable in order to build up the list of quoted values. I like to TRIM my values before quoting them so as to avoid trailing spaces in the quoted value. In your case it does not matter because all the values are three characters long and the variable is $3. A subsetting IF is used to trigger an implicit OUTPUT.
data want(keep=list); set month end=done; length list $100; retain list ''; list = catx(',', list, quote(trim(mon),"'")); if done; run; proc print data=want; run;
Output
Two ways to accomplish this.
Use a data step and as you go through each line add the value to the list incrementally
Transpose your data to a wide format and use the CATX() function on the resulting variables.
Both are illustrated below, with a case where you may need to do it by group.
*create sample data for demonstration;
data have;
infile cards dlm='09'x;
input OrgID Product $ States $;
cards;
1 football DC
1 football VA
1 football MD
2 football CA
3 football NV
3 football CA
;
run;
*Sort - required for both options;
proc sort data=have;
by orgID;
run;
**********************************************************************;
*Use RETAIN and BY group processing to combine the information;
**********************************************************************;
data want_option1;
set have;
by orgID;
length combined $100.;
retain combined;
if first.orgID then
combined=states;
else
combined=catx(', ', combined, states);
if last.orgID then
output;
run;
**********************************************************************;
*Transpose it to a wide format and then combine into a single field;
**********************************************************************;
proc transpose data=have out=wide prefix=state_;
by orgID;
var states;
run;
data want_option2;
set wide;
length combined $100.;
combined=catx(', ', of state_:);
run;
Here is another way, similar to @Reeza transpose, but done in a single step. I call the approach 'extrusion' because I envision pushing down the data set and the values get pushed to the right..
data want(keep=list); set months end=done; array months(12) $5 _temporary_; months(_n_) = quote(mon,"'"); if done; list = catx(',', of months(*)); run;
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 25. 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.