SAS query to transpose the column to single column?

srikanthyadav44 — Mon, 21 Nov 2022 05:57:50 GMT

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

Re: SAS query to transpose the column to single column?

PeterClemmensen — Mon, 21 Nov 2022 08:07:07 GMT

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;

topic Re: SAS query to transpose the column to single column? in SAS Programming

SAS query to transpose the column to single column?

Re: SAS query to transpose the column to single column?