topic Re: Collapse one column into one row in SAS Programming

Collapse one column into one row

vxhong17 — Sun, 18 Sep 2016 13:25:41 GMT

I have an input in excel like this

Symbol

KO:SGL

KO:KAW

KO:HDR

KO:NHN

KO:CLZ

KO:HYI

KO:HAC

KO:AMN

KO:SFC

KO:PIS

KO:KAM

KO:SHB

I want to have an output like this.

Symbol
KO:SGL, KO:KAW, KO:HDR, KO:NHN, KO:CLZ, KO:HYI, KO:HAC, KO:AMN, KO:SFC, KO:PIS, KO:KAM, KO:SHB

Do you know any way to do so?

Thank you,

Re: Collapse one column into one row

Tom — Sun, 18 Sep 2016 14:48:07 GMT

Depends on wha t you mean by output. You can easily write that to the log or a flat file.

libname myxls xlsx 'sample.xlsx';
data _null_;
  set myxls.sheet1 ;
  file log dsd ;
  put symbol @ ;
run;

Or to a macro variable.

proc sql noprint;
  select symbol into :mylist separated by ','
    from myxls.sheet1
  ;
quit;

Or even a trival one observation dataset.

data want ;
   length symbols $500 ;
   set myxls.sheet1 end=eof;
   symbols=catx(',',symbols,symbol);
   if eof then output;
   retain symbols;
   keep symbols;
run;

Re: Collapse one column into one row

Cynthia_sas — Sun, 18 Sep 2016 15:41:47 GMT

Or, use PROC TRANSPOSE (which by default will name the new columns COL1....COL??) -- but you can change the prefix used for the new variable names.

cynthia

Re: Collapse one column into one row

vxhong17 — Sun, 18 Sep 2016 16:42:12 GMT

Hi Tom,

Thank you so much for your detailed response !!!

Re: Collapse one column into one row

vxhong17 — Sun, 18 Sep 2016 16:47:56 GMT

Hi Cynthia.

Thank you for your sugguestion,

Best,