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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.