topic Re: Month list separated by ',' ( 'Jan','Feb','Mar',Apr','May.......,'Dec') in SAS Programming

Month list separated by ',' ( 'Jan','Feb','Mar',Apr','May.......,'Dec')

anjicamsas — Thu, 07 May 2020 12:09:07 GMT

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;

Re: Month list separated by ',' ( 'Jan','Feb','Mar',Apr','May.......,'Dec')

Ksharp — Thu, 07 May 2020 12:10:53 GMT


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 ;

Re: Month list separated by ',' ( 'Jan','Feb','Mar',Apr','May.......,'Dec')

ed_sas_member — Thu, 07 May 2020 13:38:45 GMT

Hi @anjicamsas

Depending on the situation, you might want to

output a macrovariable: the solution provided by @Ksharp is the best way to achieve this using proc sql

proc sql noprint;
	select quote(strip(Mon),"'")
	/* Put the list into a macrovariable*/
	into: list_month separated by ", "
	from Month;
quit;

%put &list_month.;

output a new variable in your dataset (+ optionally put the value into a macrovariable). in this case you can use the following approach:

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,

Re: Month list separated by ',' ( 'Jan','Feb','Mar',Apr','May.......,'Dec')

RichardDeVen — Thu, 07 May 2020 13:02:16 GMT

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

Re: Month list separated by ',' ( 'Jan','Feb','Mar',Apr','May.......,'Dec')

Reeza — Thu, 07 May 2020 15:38:32 GMT

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;

Re: Month list separated by ',' ( 'Jan','Feb','Mar',Apr','May.......,'Dec')

RichardDeVen — Thu, 07 May 2020 17:41:13 GMT

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;