topic Create new column from existing columns in SAS Enterprise Guide

Create new column from existing columns

DME790 — Mon, 22 Jan 2018 23:33:10 GMT

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

Group1	Group2	Group3	Group4	Group5
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

The output I need is:

Group1	Group2	Group3	Group4	Group5	Combined
AA	BA	CA	DA	EA	AA
AB	BB	CB	DB	EB	AB
AC	BC	CC	DC	EC	AC
AD	BD	CD	DD	ED	AD
AE	BE	CE	DE	EE	AE
AF	BF	CF	DF	EF	AF
AG	BG	CG	DG	EG	AG
AH	BH	CH	DH	EH	AH
AI	BI	CI	DI	EI	AI
AJ	BJ	CJ	DJ	EJ	AJ
					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

Re: Create new column from existing columns

novinosrin — Mon, 22 Jan 2018 23:39:05 GMT

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;

Re: Create new column from existing columns

Astounding — Mon, 22 Jan 2018 23:54:12 GMT

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;