BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vxhong17
Calcite | Level 5

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Tom
Super User Tom
Super User

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;
Cynthia_sas
SAS Super FREQ
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
vxhong17
Calcite | Level 5

Hi Cynthia.

 

Thank you for your sugguestion,

 

Best,

vxhong17
Calcite | Level 5

Hi Tom,

 

Thank you so much for your detailed response !!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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