Desktop productivity for business analysts and programmers

Create new column from existing columns

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 108
Accepted Solution

Create new column from existing columns

Hi All,

 

I need to make a combined list of all the columns. I can make a list based on each individual column using Proc SQL.

Is there a way I can make one long list of all the columns called Combined (Group1 Group2 etc) 

 

Proc SQL;
	Select QUOTE(TRIM(Group1)) INTO :Group1List
		Separated by " "
		From Test;
Quit;

 

Do I need to create a new column first and then use Proc SQL to create the list? Is there a simpler way of getting to what I need?

 

Data test;
	Input Group1 $ Group2 $ Group3 $ Group4 $ Group5 $;
		Datalines;
AA BA CA DA EA
AB BB CB DB EB
AC BC CC DC EC
AD BD CD DD ED
AE BE CE DE EE
AF BF CF DF  EF
AG BG CG DG EG
AH BH CH DH EH
AI BI CI DI EI
AJ BJ CJ DJ EJ
;
RUN;

 

 

Which gives me

Group1Group2Group3Group4Group5
AABACADAEA
ABBBCBDBEB
ACBCCCDCEC
ADBDCDDDED
AEBECEDEEE
AFBFCFDFEF
AGBGCGDGEG
AHBHCHDHEH
AIBICIDIEI
AJBJCJDJEJ

 

The output I need is:

 

Group1Group2Group3Group4Group5Combined
AABACADAEAAA
ABBBCBDBEBAB
ACBCCCDCECAC
ADBDCDDDEDAD
AEBECEDEEEAE
AFBFCFDFEFAF
AGBGCGDGEGAG
AHBHCHDHEHAH
AIBICIDIEIAI
AJBJCJDJEJAJ
     BA
     BB
     BC
     BD
     BE
     BF
     BG
     BH
     BI
     BJ
     CA
     CB
     CC
     CD
     CE
     CF
     CG
     CH
     CI
     CJ
     DA
     DB
     DC
     DD
     DE
     DF
     DG
     DH
     DI
     DJ
     EA
     EB
     EC
     ED
     EE
     EF
     EG
     EH
     EI
     EJ

 

 

Thanks in advance

 

Dean


Accepted Solutions
Solution
‎01-22-2018 07:07 PM
PROC Star
Posts: 1,357

Re: Create new column from existing columns

Data test;
	Input Group1 $ Group2 $ Group3 $ Group4 $ Group5 $;
		Datalines;
AA BA CA DA EA
AB BB CB DB EB
AC BC CC DC EC
AD BD CD DD ED
AE BE CE DE EE
AF BF CF DF  EF
AG BG CG DG EG
AH BH CH DH EH
AI BI CI DI EI
AJ BJ CJ DJ EJ
;
RUN;

proc transpose data=test out=waw;
var group:;
run;

proc transpose data=waw out=waw1;
by _name_;
var col:;
run;

data final_want;
merge test waw1(keep=col1);
run;

View solution in original post


All Replies
Solution
‎01-22-2018 07:07 PM
PROC Star
Posts: 1,357

Re: Create new column from existing columns

Data test;
	Input Group1 $ Group2 $ Group3 $ Group4 $ Group5 $;
		Datalines;
AA BA CA DA EA
AB BB CB DB EB
AC BC CC DC EC
AD BD CD DD ED
AE BE CE DE EE
AF BF CF DF  EF
AG BG CG DG EG
AH BH CH DH EH
AI BI CI DI EI
AJ BJ CJ DJ EJ
;
RUN;

proc transpose data=test out=waw;
var group:;
run;

proc transpose data=waw out=waw1;
by _name_;
var col:;
run;

data final_want;
merge test waw1(keep=col1);
run;
Super User
Posts: 6,543

Re: Create new column from existing columns

Untested, but ought to work:

 

proc sql;

select catx(' ', quote(trim(group1)), quote(trim(group2)), quote(trim(group3)), quote(trim(group4)), quote(trim(group5)))

into : grouplist separated by ' ' from test;

quit;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 170 views
  • 3 likes
  • 3 in conversation