cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
MV04
‎01-20-2020
  • 32 Posts
  • 1 Likes Given
  • 1 Solutions
  • 0 Likes Received

Re: how do I count difference in dates and a counter on that

by Obsidian | Level 7 in SAS Programming
‎12-21-2019 06:12 AM
‎12-21-2019 06:12 AM
Hi KurtBremser,   Did you get information you were looking for from my response.   Any assistance will be highly appriciated.   Thanks ... View more

Re: how do I count difference in dates and a counter on that

by Obsidian | Level 7 in SAS Programming
‎12-19-2019 04:12 PM
‎12-19-2019 04:12 PM
From where do you derive "count of ipqm1"? --- we need to derive it in the code as "every time the code =ipqm1 switches from to cde= ipqm2 the value of count is increased to 1so fisrt month say dec2018 when the process started we got account say 1111 as flag "1" and we set the code=ipqm1 then now we need to write a generic program where we need to decide from the next month the value of the code which would depend on the months difference.   I also see no variable code in your dataset, but you use it in your "else if" condition ---Like I mentioned above the code needs to be determined in the program itself and hence would populate in the target dataset. code value (ipqm1,ipqm2,ipqm3) should be unique per id in a givem month.first time the code value is set in the program as ipqm1 and save it as the first dataset in the history. here the things need to be considered from second month onwards are: 1. the number of times(count) the code value changed for an id in last 15 months 2. for a fresh month check for an id  if value of code in the history dataset is ipqm1 and the difference in the months as in from current month to the month when the value of code is ipqm1 is 1 then set code = ipmq2    otherwise check if the value of code in the history dataset is ipqm2 and the difference of month when the value is ipqm2 from current month is 2 then set code = ipmq3    oterwise if the value of code in the history dataset is ipqm3 and the difference of month when the value is ipqm3 from current month is 6 then set code = ipmq1  otherwise  if (the value of code in the history dataset is ipqm1 and month differnce as above > 1) or  (the value of code in the history dataset is ipqm2 and month differnce as above > 2) or (the value of code in the history dataset is ipqm3 and month differnce as above > 6) then code = ipqm1 ... View more

Re: how do I count difference in dates and a counter on that

by Obsidian | Level 7 in SAS Programming
‎12-19-2019 07:41 AM
‎12-19-2019 07:41 AM
Code is what we need to build in the code as what will be the value of code in a given month for a given ID consiering all those if then. Every month we would receive a dataset as 1111,1 1211,0 1311,0 1411,1 1511,1 1611,1 1711,1 1811,1 1911,0 2011,0 2111,1   and we need to create target dataset as given above ... View more

how do I count difference in dates and a counter on that

by Obsidian | Level 7 in SAS Programming
‎12-19-2019 05:20 AM
‎12-19-2019 05:20 AM
Hi All, I have got a datset as below %let report_month = %sysfunc(putn(%sysfunc(today()),monyy7.)); %let report_date = %sysfunc(putn(%sysfunc(today()),date9.)); %let report_date_beg = %sysfunc(putn(%sysfunc(intnx(month,"&report_date"d,0,b)),date9.)); %put report_month : &report_month. report_date : &report_date. report_date_beg :&report_date_beg.; data disp_elg; infile datalines dlm = "," dsd; input id $ flag; rep_month = symget("report_month"); datalines;   1111,1 1211,0 1311,0 1411,1 1511,1 1611,1 1711,1 1811,1 1911,0 2011,0 2111,1 ;run; This dataset gives me flag as 1 and 0 per ID and reporting month . I need to produce a dataset and retain a history for 15 months where the varibales would be like mentioned in the target dataset if flag is 1 and count of ipqm1 in last 15 month  = 0 then code = ipqm1 else if flag is 1 and count of ipqm1 > 2 in last 10 months then code = ipqm2 else if flag is 1 and  code = ipqm1 and diff_month = 1 then  code = ipqm2 else if code = ipqm2 and diff_month = 2 then code = ipqm3 else if code = ipqm3 and diff_month = 6 then code = ipqm1 else if (code = ipqm1 and diff_month > 1) or (code = ipqm2 and diff_month > 2) or (code = ipqm3 and diff_month > 6) then code = ipqm1 else "do nothing"   So basically we need to set the code depending on the differnce in months and count of particular code  in that month diff and the data needs to be produced on a monthly basis and get that appended to create the history. Any support  would be highly appriciated     Target ID,code,rep_month 1111,ipqm1,dec2018 1211,ipqm1,dec2018 1311,ipqm1,dec2018 1411,ipqm1,dec2018 1511,ipqm1,dec2018 1611,ipqm1,dec2018 1711,ipqm1,dec2018 1811,ipqm1,dec2018 1911,ipqm1,dec2018 2011,ipqm1,dec2018 2111,ipqm1,dec2018 2211,ipqm1,Jan2019 1411,ipqm2,Jan2019 2311,ipqm1,Jan2019 1211,ipqm2,Jan2019 2411,ipqm1,Jan2019 2511,ipqm1,Jan2019 2611,ipqm1,Jan2019 2711,ipqm1,Jan2019 2811,ipqm1,Jan2019 2911,ipqm1,Jan2019 3011,ipqm1,Jan2019 3111,ipqm1,Feb2019 3211,ipqm1,Feb2019 3311,ipqm1,Feb2019 1311,ipqm3,Feb2019 3411,ipqm1,Feb2019 3511,ipqm1,Feb2019 3611,ipqm1,Feb2019 3711,ipqm1,Feb2019 3811,ipqm1,Feb2019 3911,ipqm1,Feb2019 ... View more

Re: joining on same variable date n key but different values

by Obsidian | Level 7 in SAS Programming
‎07-26-2019 09:41 AM
‎07-26-2019 09:41 AM
how do increase the range ... View more

Re: joining on same variable date n key but different values

by Obsidian | Level 7 in SAS Programming
‎07-26-2019 06:25 AM
‎07-26-2019 06:25 AM
Patrick I have sent a better data basicaly duplicate of amount on diff dates ... View more

Re: joining on same variable date n key but different values

by Obsidian | Level 7 in SAS Programming
‎07-26-2019 06:07 AM
‎07-26-2019 06:07 AM
Hi Parick,   Below are the dataset    data d1; infile cards dsd truncover ; input id $ amount date:ddmmyy10.; cards; aa,50,06/06/2019 aa,50,07/06/2019 aa,20,08/06/2019 aa,40,09/06/2019 bb,10,03/06/2019 bb,20,04/06/2019 bb,30,08/06/2019 ;run; data d2; infile cards dsd dlm= "," missover; input id $ amount date:ddmmyy10. curr $; cards; aa,50,07/06/2019,gbp aa,50,08/06/2019,gbp aa,20,10/06/2019,gbp aa,40,09/06/2019,gbp aa,60,12/06/2019,gbp bb,10,02/06/2019,gbp bb,20,04/06/2019,gbp bb,30,08/06/2019,gbp ;run;   and the output soule be  ... View more

Re: joining on same variable date n key but different values

by Obsidian | Level 7 in SAS Programming
‎07-26-2019 05:16 AM
‎07-26-2019 05:16 AM
also I was joining on id ,amount as a left join but getting loads of dups ... View more

Re: joining on same variable date n key but different values

by Obsidian | Level 7 in SAS Programming
‎07-26-2019 05:15 AM
‎07-26-2019 05:15 AM
Thanks for the response Patrik,   Both datasets are from different sources one may be running on weekdays and getting dates as the process day and if it is a weekend or a bank holiday then dates are skipping those  whereas other may be running on from tues- saturday something like that hence they are different so id and amount is same but for some records when ID and amount is same date is same as well whereas for others id and amont is same but date is different  ... View more

joining on same variable date n key but different values

by Obsidian | Level 7 in SAS Programming
‎07-26-2019 04:48 AM
‎07-26-2019 04:48 AM
Hi All , I have got two datasets as below  dataset 1: id amount date aa 50 06/06/2019 aa 10 07/06/2019 aa 20 08/06/2019 aa 40 09/06/2019 bb 10 03/06/2019 bb 20 04/06/2019 bb 30 08/06/2019   Dataset 2 id amount date curr aa 50 07/06/2019 gbp aa 10 08/06/2019 gbp aa 20 10/06/2019 gbp aa 40 09/06/2019 gbp aa 60 12/06/2019 gbp bb 10 02/06/2019 gbp bb 20 04/06/2019 gbp bb 30 08/06/2019 gbp     I need all records from Datset1 basically a left join but when I am trying to join on Id and amount I am getting loads of dups  amount matches to the date but both datasets have got different dates.Therefore when I am trying to match  them left join is making loads of dups. I need an urgent help for this query . Any any guidance will be much appreciated. ... View more

Transpose data in 5 variables

by Obsidian | Level 7 in New SAS User
‎07-25-2019 07:35 AM
‎07-25-2019 07:35 AM
Hi All,   I would really appreciate if someone could help me with the following. I need to transpose below data    GRP_a GRP_B GRP_C GRP_d GRP_E Total_Value Total_Volume abc key ColX   aa GBP 39,775,288 1634267 abc key ColX   bb GBP 142,123,608 722948 abc key ColX   cc GBP 813,636,979 22434774     to this format    GRP_a GRP_B GRP_C GRP_d aa_Value aa_Vol bb_Value bb_Volume cc_Value cc_Volume abc key ColX   GBP 39,775,288 1634267 ########### 722948 ######## 22434774 ... View more

Re: how to stop do loop on a condition

by Obsidian | Level 7 in New SAS User
‎06-11-2019 06:49 AM
‎06-11-2019 06:49 AM
Hi Draycut Are you not counting the first negative sal ? ... View more

Re: how to stop do loop on a condition

by Obsidian | Level 7 in New SAS User
‎06-11-2019 05:46 AM
‎06-11-2019 05:46 AM
Hi Draycut,   If last two sal are negative then it is counting it two times than for example in the below example last two sal are negative so instead of 3 count is coming as 4 for this ID ID SAL AD04 -286.32 AD04 -286.32 AD04 -165.21 AD04 -98.37 AD04 -78.37 AD04 -69.17 AD04 -69.17 AD04 97.83 AD04 138.75 AD04 133.25 AD04 179.31 AD04 179.31 AD04 201.36 AD04 211.36 AD04 211.36 AD04 211.36 AD04 -88.64 AD04 125.97 AD04 183.47 AD04 -1674.9 AD04 -1169.32 ... View more

Re: how to stop do loop on a condition

by Obsidian | Level 7 in New SAS User
‎06-11-2019 05:12 AM
‎06-11-2019 05:12 AM
Hi Draycut, Thanks so much for the above. Now if I need to find out average number of times ID went from negative to positive for sal across all ID's then how do I do it ... View more

how to stop do loop on a condition

by Obsidian | Level 7 in New SAS User
‎06-11-2019 04:19 AM
‎06-11-2019 04:19 AM
Hi All, I realy need an urgent help on the below. I have got a dataset as below and I want to create a counter when sal goes from positive to negative and counter increases to 1 but not for all negative sal .I have explained as below. for example in the below example for ID A01 sal went from postive to negative only two times so I need to create this counter for every ID Has     Want   ID Sal   ID Count A01 261.56   A01 2 A01 261.56   A02 3 A01 -71.55       A01 158.48       A01 -36.53       A01 245.53       A01 45.53       A01 173.93       A01 229.92       A01 193.14       A01 298.86       A01 298.86       A02 299.84       A02 -209.84       A02 299.2       A02 299.2       A02 -4000.02       A02 -1472.55       A02 408.55       A02 -47.05       A02 205.94                 ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎01-20-2020 08:32 AM
Likes given to
View All
Quick links