## PROC SQL : Create many variables from one variable using case

Solved
Frequent Contributor
Posts: 85

# PROC SQL : Create many variables from one variable using case

Hi All,

Please find the code below : I want to create variables D2, D3, D4, D5,D6,D7 and D8 from variable TPjNL based on conditions... But somehow I am not able to produce it correctly... Need help on this

PROC SQL NOPRINT;

CREATE TABLE TEMP AS

SELECT

CASE  TPjNL WHEN

NM="Medical "

THEN  D2 ,

ELSE WHEN

NM="Income "

THEN D3 ,

ELSE WHEN

NM="Workers'"

THEN D4 ,

ELSE WHEN

NM="Motor "

THEN  D5 ,

ELSE  WHEN

NM="Other motor "

THEN  D6 ,

ELSE WHEN

NM="Marine"

THEN  D7 ,

ELSE WHEN

NM="General "

THEN  D8

ELSE "."

END;

QUIT;

Accepted Solutions
Solution
‎04-10-2012 01:16 PM
Super User
Posts: 8,107

## Re: PROC SQL : Create many variables from one variable using case

Your code is doing the opposite of your request. You care creating one variable from many variables.

To make many variables you would need many CASE statements.  Assuming you want to split copy TPJNL into variables D2,D3,... then use something like this:

select

case when (NM='Medical') then TPJNL else . end as D2

,case when (NM='Income') then TPJNL else . end as D3

...

All Replies
Solution
‎04-10-2012 01:16 PM
Super User
Posts: 8,107

## Re: PROC SQL : Create many variables from one variable using case

Your code is doing the opposite of your request. You care creating one variable from many variables.

To make many variables you would need many CASE statements.  Assuming you want to split copy TPJNL into variables D2,D3,... then use something like this:

select

case when (NM='Medical') then TPJNL else . end as D2

,case when (NM='Income') then TPJNL else . end as D3

...

🔒 This topic is solved and locked.