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

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) 
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
ballardw
Super User

Have you already read this data into SAS?

cmoore
Obsidian | Level 7

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.

ballardw
Super User

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;
cmoore
Obsidian | Level 7

Thanks a lot for your help. Works great.

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
  • 828 views
  • 0 likes
  • 2 in conversation