flagging an observation

[rakeshvvv]

Hi All,

Can someone help me with a below query. I have a dataset with 4 variables. ID AVG SALES YEAR. I want to create flag variable which shows consecutive increase of sale of 30 or greater for 3 consecutive months from AVG='PRE' for respective id. I have created below sample and indicated the records which needs to be flagged 

hEID AVG SALES YEAR/MONTH FLAG

01            99     2008/1
                98     2008/2
                96     2008/3
                99     2008/4
                94     2008/5
       PRE 97.2   2008/6
              107      2008/7
              110      2008/8
              160     2008/8
              133    2008/9
              135    2008/10          Y
              112    2008/11
              108    2008 /12
             
02          100 2008/1
              101 2008/2
              102 2008/3
              103 2008/4

              104 2008/5
      PRE 102 2008/6
              90 2008/7
             101 2008 /8
            100 2008/9
            135 2008/10
            140 2008/11
            145 2008/12        Y
            

 Thanks in advance.

Rakesh

[PeterClemmensen]

Do you have SAS/ETS?

[rakeshvvv]

Yes I have SAS EG.

[novinosrin]

@rakeshvvv  Hi, @PeterClemmensen  asked if you have SAS ETS not EG. 

Try running 

proc setinit;run;

and look at the log:

You should get a report like the following

Original site validation data
Current version: 9.04.01M5P091317
Site name: 'DEPAUL UNIVERSITY-SFA T&R'.
Site number: 70009223.
Expiration: 30JUN2019.
Grace Period: 45 days (ending 14AUG2019).
Warning Period: 46 days (ending 29SEP2019).
System birthday: 31MAY2018.
Operating System: W32_WKS .
Product expiration dates:
---Base SAS Software
30JUN2019
---SAS/STAT
30JUN2019
---SAS/GRAPH
30JUN2019
---SAS/ETS
30JUN2019
---SAS/FSP
30JUN2019
---SAS/OR
30JUN2019
---SAS/AF
30JUN2019
---SAS/IML
30JUN2019
---SAS/QC
30JUN2019
---SAS/SHARE
30JUN2019
---SAS/ASSIST
30JUN2019
---SAS/CONNECT
30JUN2019
---SAS/EIS
30JUN2019
---SAS/GIS
30JUN2019
---SAS/SHARE*NET
30JUN2019
---MDDB Server common products
30JUN2019
---SAS Integration Technologies
30JUN2019
---SAS/Secure 168-bit
30JUN2019
---SAS/Secure Windows
30JUN2019
---SAS Enterprise Guide
30JUN2019
---SAS Bridge for ESRI
30JUN2019
---OR OPT
30JUN2019
---OR PRS
30JUN2019
---OR IVS
30JUN2019
---OR LSO
30JUN2019
---SAS/ACCESS Interface to DB2
30JUN2019
---SAS/ACCESS Interface to Oracle
30JUN2019
---SAS/ACCESS Interface to SAP ASE
30JUN2019
---SAS/ACCESS Interface to PC Files
30JUN2019
---SAS/ACCESS Interface to ODBC
30JUN2019
---SAS/ACCESS Interface to OLE DB
30JUN2019
---SAS/ACCESS Interface to Teradata
30JUN2019
---SAS/ACCESS Interface to Microsoft SQL Server
30JUN2019
---SAS/ACCESS Interface to MySQL
30JUN2019
---SAS/IML Studio
30JUN2019

[rakeshvvv]

I am sorry...I checked and I dont have the SAS/ETS

[novinosrin]

I am unable to spot this "increase of sale of 30 or greater for 3 consecutive months " on your data sample. I don't know where I am missing

[rakeshvvv]

I meant to say records having greater then 30 value consecutively from baseline value when AVG='PRE'. In the below example 97.2 is the average and 160,133,135 are greater then AVG 97.2 by 30 and last record of 135 is flagged Y. I hope I explained it better this time.

  PRE 97.2   2008/6
              107      2008/7
              110      2008/8
              160     2008/8
              133    2008/9
              135    2008/10          Y

[novinosrin]

Thank you & understood. Can you provide your sample in the form of a datastep or at least an excel sheet attachment coz I am unable to copy paste to datalines for list input 

[rakeshvvv]

It is company sensitive data and we are not allowed to post in public forums. It would be helpful if you can provide sas code based on sample data i have provided.

[novinosrin]

I meant the mock data not the real data. Of course I do understand

[rakeshvvv]

ID	AVG	SALES	FLAG	year	month	flag
1002		99		2017	1
1002		98		2017	2
1002		96		2017	3
1002		99		2017	4
1002		94		2017	5
1002		107		2017	6
1002		133		2017	7
1002		160		2017	8
1002		133		2017	9	Y
1002		123		2017	10
1002		112		2017	11
1002		108		2017	12
1002		105		2018	1
1002		104		2017	2
1002		106		2017	3
1002		102		2017	4
1002		103		2017	5
1002		140		2017	6
1002		145		2017	7
1002		150		2017	8	Y
1002		102		2017	9
1002		103		2017	10
1002		101		2017	11
1002		102		2017	12
1002	BASE	97.2		2017