DATA Step, Macro, Functions and more

Text into new columns

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Text into new columns

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) 

Accepted Solutions
Solution
‎03-31-2017 03:06 AM
Super User
Posts: 10,500

Re: Text into new columns

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;

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Text into new columns

Have you already read this data into SAS?

Contributor
Posts: 32

Re: Text into new columns

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.

Solution
‎03-31-2017 03:06 AM
Super User
Posts: 10,500

Re: Text into new columns

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;
Contributor
Posts: 32

Re: Text into new columns

Thanks a lot for your help. Works great.

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 138 views
  • 0 likes
  • 2 in conversation