topic Text into new columns in SAS Programming

Text into new columns

cmoore — Thu, 30 Mar 2017 15:15:40 GMT

I have the data below that is contained in one column. Please see below. I would like to create two new columns with headers "Africa-Wide" and "Asia-Pacific Region" with the data directly underneath that headername. The gap in-between records means a new header.Would you happen to know how I can create the new columns? Thanks.

Data

Africa-wide

Corporate/M&A (Band 4)

Dispute Resolution (Band 2)

Projects & Energy (Band 4)

TMT (Band 1)

Asia-Pacific Region

Arbitration (International) (Band 2)

Banking & Finance (Band 3)

Capital Markets: Debt (Band 3)

Capital Markets: Equity (Band 2)

Corporate/M&A (Band 1)

Corporate/M&A: Private Equity (Band 2)

Energy & Natural Resources (Band 4)

Life Sciences (Band 3)

Projects & Infrastructure (Band 4)

TMT (Band 3)

Re: Text into new columns

ballardw — Thu, 30 Mar 2017 15:21:44 GMT

Have you already read this data into SAS?

Re: Text into new columns

cmoore — Thu, 30 Mar 2017 15:31:30 GMT

Yes as a CSV file. Just wanted to know the syntax to manipulate the data into those 2 new columns. That is just a small sample of data. I have 500 rows of data contained in the one column.

Re: Text into new columns

ballardw — Thu, 30 Mar 2017 16:00:53 GMT

A CSV file is not a SAS data set.

The real question is are there exactly two "groups" of data? If not you would need to know how many different groups there are before hand and have some fairly obnoxious code. Plus "column heading" as a variable name cannot contain dashes or spaces to the values such as "Africa-Wide" would have to be a variable label.

Generally is much better to have one or more variables that hold the group indicators and have the values appear on each row. Then that variable would be used for grouping in reports and such.

The below demonstrates this approach and one example of BY processing using the grouping variable. Use variable names you like and add variable labels for prettier output.

data have;
   infile datalines truncover dsd;
   informat string $50.;
   input string $;
datalines;
Africa-wide 
Corporate/M&A (Band 4)  
Dispute Resolution (Band 2)  
Projects & Energy (Band 4)  
TMT (Band 1)  
  
Asia-Pacific Region 
Arbitration (International) (Band 2)  
Banking & Finance (Band 3)  
Capital Markets: Debt (Band 3)  
Capital Markets: Equity (Band 2)  
Corporate/M&A (Band 1)  
Corporate/M&A: Private Equity (Band 2)  
Energy & Natural Resources (Band 4)  
Life Sciences (Band 3)  
Projects & Infrastructure (Band 4)  
TMT (Band 3)  
;
run;

data want;
   set have;
   length area $ 50;
   retain area;
   if _n_=1 then area=string;
   else if missing(area) then area=string;
   if missing(string) then call missing(area);
   if not missing(area) then output;
run;

proc print data=want noobs;
   by area;
   var string;
run;

Re: Text into new columns

cmoore — Fri, 31 Mar 2017 07:07:27 GMT

Thanks a lot for your help. Works great.