dear all
i have my data in the followng format
Group_code | comany01 | comany02 | comany03 | comany04 | comany05 |
64191 | Axis Bank Ltd. | Punjab National Bank | H D F C Bank Ltd. | Kotak Mahindra Bank Ltd. | I C I C I Bank Ltd. |
30911 | Bajaj Auto Ltd. | T V S Motor Co. Ltd. | Hero Motocorp Ltd. | Eicher Motors Ltd. | |
2513 | Bharat Heavy Electricals Ltd. | ||||
61209 | Bharti Airtel Ltd. | Reliance Communications Ltd. | Vodafone Idea Ltd. | ||
41001 | D L F Ltd. | Unitech Ltd. | Housing Development & Infrastructure Ltd. | ||
21002 | Dr. Reddy'S Laboratories Ltd. | Glaxosmithkline Pharmaceuticals Ltd. | Cipla Ltd. | Lupin Ltd. | Sun Pharmaceutical Inds. Ltd. |
52109 | G A I L (India) Ltd. | Petronet L N G Ltd. | Indraprastha Gas Ltd. | ||
64191 | H D F C Bank Ltd. | Axis Bank Ltd. | I C I C I Bank Ltd. | Punjab National Bank | State Bank Of India |
20239 | Hindustan Unilever Ltd. | Procter & Gamble Hygiene & Health Care Ltd. | Jyothy Labs Ltd. | ||
64192 | Housing Development Finance Corpn. Ltd. | L I C Housing Finance Ltd. | Dewan Housing Finance Corpn. Ltd. |
i have to transpose the five columns of the companies into a single column by group code . I need the output in the following format
code | company |
64191 | Axis Bank Ltd. |
64191 | Punjab National Bank |
64191 | H D F C Bank Ltd. |
64191 | Kotak Mahindra Bank Ltd. |
64191 | I C I C I Bank Ltd. |
30911 | Bajaj Auto Ltd. |
30911 | T V S Motor Co. Ltd. |
30911 | Hero Motocorp Ltd. |
30911 | Eicher Motors Ltd. |
2513 | Bharat Heavy Electricals Ltd. |
61209 | Bharti Airtel Ltd. |
61209 | Reliance Communications Ltd. |
61209 | Vodafone Idea Ltd. |
please suggest me an appropriate SAS CODE
i am attaching the sample input file .CSV format
thanking you in advance
Try this
data have;
input Group_code (company01 - company05)(:$100.);
infile datalines dlm = '|' dsd;
datalines;
64191|Axis Bank Ltd. |Punjab National Bank | H D F C Bank Ltd. |Kotak Mahindra Bank Ltd. |I C I C I Bank Ltd.
30911|Bajaj Auto Ltd. |T V S Motor Co. Ltd. | Hero Motocorp Ltd. |Eicher Motors Ltd. |
2513 |Bharat Heavy Electricals Ltd. | | | |
61209|Bharti Airtel Ltd. |Reliance Communications Ltd. | |Vodafone Idea Ltd. |
41001|D L F Ltd. |Unitech Ltd. | |Housing Development & Infrastructure Ltd. |
21002|Dr. Reddy'S Laboratories Ltd. |Glaxosmithkline Pharmaceuticals Ltd. | Cipla Ltd. |Lupin Ltd. |Sun Pharmaceutical Inds. Ltd.
52109|G A I L (India) Ltd. |Petronet L N G Ltd. | |Indraprastha Gas Ltd. |
64191|H D F C Bank Ltd. |Axis Bank Ltd. | I C I C I Bank Ltd. |Punjab National Bank |State Bank Of India
20239|Hindustan Unilever Ltd. |Procter & Gamble Hygiene & Health Care Ltd. | |Jyothy Labs Ltd. |
64192|Housing Development Finance Corpn. Ltd. |L I C Housing Finance Ltd. | |Dewan Housing Finance Corpn. Ltd. |
;
data want;
set have;
array c company:;
do over c;
company = c;
if company ne '' then output;
end;
drop company0:;
run;
Try this
data have;
input Group_code (company01 - company05)(:$100.);
infile datalines dlm = '|' dsd;
datalines;
64191|Axis Bank Ltd. |Punjab National Bank | H D F C Bank Ltd. |Kotak Mahindra Bank Ltd. |I C I C I Bank Ltd.
30911|Bajaj Auto Ltd. |T V S Motor Co. Ltd. | Hero Motocorp Ltd. |Eicher Motors Ltd. |
2513 |Bharat Heavy Electricals Ltd. | | | |
61209|Bharti Airtel Ltd. |Reliance Communications Ltd. | |Vodafone Idea Ltd. |
41001|D L F Ltd. |Unitech Ltd. | |Housing Development & Infrastructure Ltd. |
21002|Dr. Reddy'S Laboratories Ltd. |Glaxosmithkline Pharmaceuticals Ltd. | Cipla Ltd. |Lupin Ltd. |Sun Pharmaceutical Inds. Ltd.
52109|G A I L (India) Ltd. |Petronet L N G Ltd. | |Indraprastha Gas Ltd. |
64191|H D F C Bank Ltd. |Axis Bank Ltd. | I C I C I Bank Ltd. |Punjab National Bank |State Bank Of India
20239|Hindustan Unilever Ltd. |Procter & Gamble Hygiene & Health Care Ltd. | |Jyothy Labs Ltd. |
64192|Housing Development Finance Corpn. Ltd. |L I C Housing Finance Ltd. | |Dewan Housing Finance Corpn. Ltd. |
;
data want;
set have;
array c company:;
do over c;
company = c;
if company ne '' then output;
end;
drop company0:;
run;
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.