DATA Step, Macro, Functions and more

Collapse one column into one row

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

Collapse one column into one row

[ Edited ]

I have an input in excel like this

 

Symbol
KOSmiley FrustratedGL
KO:KAW
KO:HDR
KO:NHN
KO:CLZ
KO:HYI
KO:HAC
KO:AMN
KOSmiley FrustratedFC
KOSmiley TongueIS
KO:KAM
KOSmiley FrustratedHB

 

I want to have an output like this.

Symbol
KOSmiley FrustratedGL, KO:KAW, KO:HDR, KO:NHN, KO:CLZ, KO:HYI, KO:HAC, KO:AMN, KOSmiley FrustratedFC, KOSmiley TongueIS, KO:KAM, KOSmiley FrustratedHB

 

Do you know any way to do so?

 

Thank you,

 


Accepted Solutions
Solution
‎09-18-2016 12:38 PM
Super User
Super User
Posts: 6,500

Re: Collapse one column into one row

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎09-18-2016 12:38 PM
Super User
Super User
Posts: 6,500

Re: Collapse one column into one row

[ Edited ]

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;
SAS Super FREQ
Posts: 8,743

Re: Collapse one column into one row

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
Contributor
Posts: 51

Re: Collapse one column into one row

Hi Cynthia.

 

Thank you for your sugguestion,

 

Best,

Contributor
Posts: 51

Re: Collapse one column into one row

Hi Tom,

 

Thank you so much for your detailed response !!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 437 views
  • 1 like
  • 3 in conversation