DATA Step, Macro, Functions and more

PROC SQL : Create many variables from one variable using case

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 85
Accepted Solution

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
Super User
Posts: 6,500

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

...


View solution in original post


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

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.

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

Discussion stats
  • 1 reply
  • 171 views
  • 0 likes
  • 2 in conversation