PROC SQL : Create many variables from one variable using case

[forumsguy]

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;

[Tom · Accepted solution]

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

...