BookmarkSubscribeRSS Feed
rakeshvvv
Quartz | Level 8

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

 

 

 

 

 

10 REPLIES 10
PeterClemmensen
Tourmaline | Level 20

Do you have SAS/ETS?

rakeshvvv
Quartz | Level 8

Yes I have SAS EG.

novinosrin
Tourmaline | Level 20

@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
Quartz | Level 8

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

novinosrin
Tourmaline | Level 20

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
Quartz | Level 8

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
Tourmaline | Level 20

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
Quartz | Level 8

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
Tourmaline | Level 20

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

rakeshvvv
Quartz | Level 8
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    

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 3020 views
  • 0 likes
  • 3 in conversation