Desktop productivity for business analysts and programmers

Proc SQL case

Reply
Frequent Contributor
Posts: 125

Proc SQL case

I Have a dataset

x y and I have to create z

x     y

CA 02

PA  MX

GA  FL

BM

HI

i have to create Z variable in dataset and x and y has state codes

when y in 02 then we assign SC some state code but when not numberic then Y as it is but when missing then shd do x=y

and. Now Z.

Z

SC

MX

FL

BM

HI

        

CAn anyone pls help

Super User
Posts: 5,917

Re: Proc SQL case

Posted in reply to chennupriya

Don't understand what you are saying.

Please provide a complete result data set with X, Y and Z columns.

Data never sleeps
Frequent Contributor
Posts: 125

Re: Proc SQL case

HHi

I have state codes in X and Y but I need to create Z coloumn from Y

and Y has numeric and character data when state codes in 02 they gave me conversion that 02 means SC and 03 means VA but Y has character data also  like MX and FL then leave as it is

aand Y has missing data then Z coloumn takes value from X coloumn as Y has missing values we u notice X Y and Z which I gave u above explains it now I am stuck how to create Z

Super User
Posts: 24,004

Re: Proc SQL case

Posted in reply to chennupriya

Neither do we, unless you clarify your problem.

In your sample output above where does SC come from and why is PA missing?

Frequent Contributor
Posts: 125

Re: Proc SQL case

hi ,

ok lets go like this

state p_st

XY    PA

SC   LA

FL 

GA

I WANT A CODE WHEN P_ST IS MISSING THEN TAKE A VALUE FROM STATE OTHERWISE P_ST

Super User
Posts: 24,004

Re: Proc SQL case

Posted in reply to chennupriya

Use the coalesce function, see the documentation regarding the usage details:

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Z = coalesce(p_st, state);

Ask a Question
Discussion stats
  • 5 replies
  • 412 views
  • 0 likes
  • 3 in conversation