BookmarkSubscribeRSS Feed
micksom
Calcite | Level 5

Account

Month

Flag

A1

1

0

A1

2

1

A1

3

0

B1

1

0

B2

2

0

B3

1

1

Hi all, hopefully an easy one. Sorry this is a bit simple. I am a new SAS user!!

Is there a way to do this preferably in proc sql (but data step is ok) without creating a new column i.e. as an update function. I am just looking to make a quick fix to an existing dataset?

Where Account = A1 and month = 2 and Flag = 1 then update flag to 1 on Account A1 where month = 1.

Kind Regards,

Michael

5 REPLIES 5
ballardw
Super User

Are you looking to have the solution work for other combinations of account, month and flag for different month(s)?

micksom
Calcite | Level 5

Yes, exactly. So in the example below I would want

Where Account = A1 and month = 2 and Flag = 1 then update flag to 1 on Account A1 where month = 1.

Where Account = A1 and month = 5 and Flag = 1 then update flag to 1 on Account A1 where month = 0.


I would then need to do the same for multiple other accounts.

OLD

AccountMonthFlag
A110
A121
A130
A140
A151
B110
B121
B130
B140
B150


NEW

AccountMonthFlag
A111
A121
A130
A141
A151
B111
B121
B130
B140
B150
PGStats
Opal | Level 21

It can be done with SQL UPDATE. You will get a warning, but you can ignore it.

proc sql UNDO_POLICY=NONE;

update MYDATASET as A set Flag = 1

where exists (select * from MYDATASET where Account=A.Account and Month = A.Month+1 and Flag);

quit;

PG

PG
Haikuo
Onyx | Level 15

Here is one proc sql approach, and I can vision 2XDOW or Hash for data step solution:

DATA HAVE;

INFILE CARDS DLM='09'X;

INPUT Account $ Month Flag @@;

CARDS;

A1   1    0

A1   2    1

A1   3    0

A1   4    0

A1   5    1

B1   1    0

B1   2    1

B1   3    0

B1   4    0

B1   5    0

;

PROC SQL;

SELECT ACCOUNT, MONTH, CASE WHEN EXISTS (SELECT * FROM HAVE WHERE ACCOUNT=A.ACCOUNT AND MONTH=A.MONTH+1 AND FLAG=1) THEN 1 ELSE FLAG END AS FLAG

FROM HAVE A

;QUIT;

Regards,

Haikuo

micksom
Calcite | Level 5

Thanks for the help guys. Just trying how to work out how to put it into practice.

Two questions:

1. Generally is it possible to do a where statement that will filter all those account which match two criteria  e.g. where account = a.account AND(month = 1 and flag = 0) AND (where month = 2 and flag = 1)

2. In the example provided above MONTH=A.MONTH+1 ,would that still work ok when i have 201203

Michael

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3196 views
  • 0 likes
  • 4 in conversation