About Adnan_Razaq

ballardw · ‎04-16-2019

16? What is the question?

AMSAS · ‎04-11-2019

Hi Adnan_Razaq You could use the point set statement option to process the dataset (see below) Be warned Continuous loops can occur when you use the POINT= option. data want ; /* Read the Dataset to determine number of observations (nobs set statement option) */ set have nobs=numberObs ; /* Set up a loop (i) to process all the observations in the dataset */ do i=1 to numberObs ; /* variable to point 12 observations ahead */ plus12=i+12 ; /* Only look ahead when there are 12 more observations in the dataset */ if plus12<=numberObs then do ; /* Read the bad_flag for the 12th observation ahead of the current observation */ /* rename bad_flag to bad_flag_in12 */ set have(keep=bad_flag rename=(bad_flag=bad_flag_in12)) point=plus12 ; end ; /* Read the current observation */ set have point=i ; /* If current observation bad_flag=1 then force bad_flag_in12=0 */ if bad_flag=1 then bad_flag_in12=0 ; output want ; end ; /* Stop processing - Otherwise this will loop */ stop ; run ;

Kurt_Bremser · ‎07-20-2018

Don't expect any order you did not force. See Maxim 31.

Ksharp · ‎05-31-2018

data have; input reporting_date account_id $ repayment_type $; informat reporting_date date9.; format reporting_date date9.; cards ; 31JAN2018 123 REP 31JAN2018 456 INT 31JAN2018 789 PAP 28FEB2018 123 REP 28FEB2018 456 . 28FEB2018 789 PAP 31MAR2018 123 . 31MAR2018 456 . 31MAR2018 789 . ; run; data want; set have ; by reporting_date; if first.reporting_date then n=0; n+1; if n=1 then repayment_type='REP'; if n=2 then repayment_type='INT'; if n=3 then repayment_type='PAP'; drop n; run;

Kurt_Bremser · ‎05-16-2018

Two approaches, SQL and data step, that produce the same result: data have; input account_id $ Sub_no $ reporting_date conversion_date; informat reporting_date conversion_date date9.; format reporting_date conversion_date date9.; cards; 12345 1 31JAN2018 . 12345 2 31JAN2018 . 12345 3 31JAN2018 . 67891 1 31JAN2018 . 67891 2 31JAN2018 . 12345 1 28FEB2018 28FEB2018 12345 2 28FEB2018. 12345 3 28FEB2018 . 67891 1 28FEB2018 . 67891 2 28FEB2018 28FEB2018 ; run; proc sql; create table want1 as select account_id, reporting_date, max(conversion_date) as conversion_date format=date9. from have group by reporting_date, account_id; quit; data want2; set have (rename=(conversion_date=_cvdate)); by reporting_date account_id; retain conversion_date; format conversion_date date9.; if _cvdate ne . then conversion_date = _cvdate; if last.account_id then output; drop _cvdate sub_no; run;

Reeza · ‎04-24-2018

It depends on what you want. Round will round the variable to the nearest second decimal place in this case. I think this is the same as using 8.2 format to display your data. However, 8.2 only controls the display, the value in the table would still be the actual value, so it depends on what you're trying to do overall.

mkeintz · ‎04-03-2018

Here's a way to record the information you want, but uses a common date variable DATE. Instead of maintaining values for added_date and removed_date, it creates a new variable DATE_TYPE (='A' for Added_date, 'R' for removed_date, or 'B' for both). But if you really want the data layout you describe, use the same MERGE statement with the rename parameter as below, but instead of creating DATE_TYPE, recreate added_date and removed_date: data want; merge added (rename=(added_date=date) in=inA) removed (rename=(removed_date=date) in=inR); if inA=1 and inR=1 then date_type='B' ; /*for both*/ else if inA then date_type='A'; else date_type='R'; run;

FredrikE · ‎02-05-2018

Many answers here....and here's another one 🙂 data want; set have; length month 8; retain lowest_rental_value; month = put(received_date, yymmn6.); if _n_ = 1 then lowest_rental_value = 100000000; if rental_value < lowest_rental_value then lowest_rental_value = rental_value; run; proc sql; create table want as select account_id, month, min(lowest_rental_value) as lowest_rental_value from want group by 1,2 ; quit; //Fredrik

RW9 · ‎01-26-2018

An alternative to @Kurt_Bremser's suggestion of using a macro variable would be: proc transpose data=have out=want (rename= (_NAME_ = reporting_date)); by account_id sub_no; var jan: feb: mar: apr: may: jun: jul: aug: sep: oct: nov: dec:; run; what this does is use the : truncation, so any variable beginning with jan will be transposed, any beginning with feb etc. You could alo use a list, if you know first and last column: proc transpose data=have out=want (rename= (_NAME_ = reporting_date)); by account_id sub_no; var jan2000--dec2017; run;

ballardw · ‎11-30-2017

Proc Compare is one tool that provides that information (as well as if the same-named variables are of different types). Proc compare base=datasetA compare=datasetb novalues listvar; run;

mkeintz · ‎10-26-2017

You want payment day on day PD of the month. If the month is too short to have day PD, then use end-of-month: data have; input account_id reporting_date payment_day; informat reporting_date date9.; payment_date= min(intnx('month',reporting_date,-1,'end')+payment_day ,intnx('month',reporting_date,0,'end')); format reporting_date payment_date date9.; cards; 1000 31JAN2017 31 1000 28FEB2017 31 1000 31MAR2017 31 1000 30APR2017 31 1000 31MAY2017 31 2000 31JAN2017 30 2000 28FEB2017 30 2000 31MAR2017 30 2000 30APR2017 30 ; run;

JosvanderVelden · ‎10-24-2017

You could use something like the following: payment_due_date = mdy(month(reporting_date), payment_day, year(reporting_date)); Best regards, Jos

Kurt_Bremser · ‎10-19-2017

First of all: Please, and I mean PLEASE! use either the {i} or "little running man" icons (6th and 7th above the posting window) for code. Your example data code looks like this after pasting into my Enterprise Guide: data have; input account_id reporting_date sales_date sale_amount admin_cost fines interest ; 　 INFORMAT reporting_date sales_date date9.; format reporting_date sales_date date9.; cards; 1000 31JAN2017 . . . . . 1000 28FEB2017 . . . . . 1000 31MAR2017 06MAR2017 100000 1000 500 200 1000 30APR2017 06MAR2017 100000 1500 600 220 1000 31MAY2017 06MAR2017 100000 1100 200 300 2000 31JAN2017 . . . . . 2000 28FEB2017 24FEB2017 80000 50 200 365 2000 31MAR2017 24FEB2017 80000 98 25 36 2000 30APR2017 24FEB2017 80000 150 65 96 ; RUN; and produces an error when submitted. Unusable garbage. After going over it, this is what it should look like: data have; input account_id reporting_date sales_date sale_amount admin_cost fines interest; informat reporting_date sales_date date9.; format reporting_date sales_date date9.; cards; 1000 31JAN2017 . . . . . 1000 28FEB2017 . . . . . 1000 31MAR2017 06MAR2017 100000 1000 500 200 1000 30APR2017 06MAR2017 100000 1500 600 220 1000 31MAY2017 06MAR2017 100000 1100 200 300 2000 31JAN2017 . . . . . 2000 28FEB2017 24FEB2017 80000 50 200 365 2000 31MAR2017 24FEB2017 80000 98 25 36 2000 30APR2017 24FEB2017 80000 150 65 96 ; run; Given your subsequent post, I think this is what you want: data want; set have; by account_id; retain k_admin_cost k_fines k_interest; if first.account_id then do; k_admin_cost = .; k_fines = .; k_interest = .; end; if k_admin_cost ne . then admin_cost = k_admin_cost; else if admin_cost ne . then k_admin_cost = admin_cost; if k_fines ne . then fines = k_fines; else if fines ne . then k_fines = fines; if k_interest ne . then interest = k_interest; else if interest ne . then k_interest = interest; drop k_admin_cost k_fines k_interest; run;

Erkika · ‎09-15-2017

This is a very curious output you want, but here's my crack at it. Since you have missing values in the withdrawn date, create a copy of base_2 with today's date in the withdrawn date; merge that dataset to base_1 based to get the instructed date; then merge that new dataset with base_2 to get the withdrawn date. data base_2a; set base_2; if sols_withdrawn_date eq . then sols_withdrawn_date = today(); run; proc sql; create table merge as select base_1.*, base_2a.sols_instructed_date from base_1 left join base_2a on base_1.ID=base_2a.ID and month(base_1.month) >= month(base_2a.sols_instructed_date) and month(base_1.month) <= month(base_2a.sols_withdrawn_date) ; create table want as select merge.*, base_2.sols_withdrawn_date from merge left join base_2 on merge.ID = base_2.ID and month(merge.month) = month(base_2.sols_withdrawn_date) order by ID, month; quit;

Adnan_Razaq · ‎08-09-2017

The output which I require should look like Want Acc Possession_date1 1 29NOV2011 2 29MAR20017 The dataset contains either or both of the effective_date and possession_date. If either are missing the popukated date is to be used. Thanks Adnan

Online Status	Offline
Date Last Visited	‎05-23-2019 03:13 PM

Proc delete

Bad in the next 12 months flag

Difference in outputs between datastep and proc sql

Retaining values

Aggregating by a variable

Re: grouping by 2 variables

grouping by 2 variables

Re: Minimum values by reporting date

Minimum values by reporting date

Rounding down by 2 dp

Re: grouping by 2 variables

Re: Proc delete

Re: Bad in the next 12 months flag

Re: Difference in outputs between datastep and proc sql

Re: Retaining values

Re: Aggregating by a variable

Re: Rounding down by 2 dp

Re: grouping by 2 variables

Re: Minimum values by reporting date

Re: Transposing dataset with 100 + variables

Re: Comparing and listing varaibles from two seperate datsets

Re: Calculate Payment due date based on payment day

Re: Creating date

Re: Retaining values by a condition

Re: Active arrangements within reporting month

Re: selecting dates based on a condition

Risk & Finance Analytics