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) |
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;
Have you already read this data into SAS?
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.
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;
Thanks a lot for your help. Works great.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.