Desktop productivity for business analysts and programmers

Proc SQL case

Reply
Frequent Contributor
Posts: 123

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,382

Re: Proc SQL case

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: 123

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: 19,051

Re: Proc SQL case

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: 123

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: 19,051

Re: Proc SQL case

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
  • 348 views
  • 0 likes
  • 3 in conversation