About Steelers_In_DC

Steelers_In_DC · ‎02-27-2015

The dates will only go from June to December of the same year, at the longest. I agree that this is not the best way to display the data but that is what is requested. I would like to use arrays but am not very familiar. I am updating the info with proc transpose but not getting it quite right, that's why I posted here.

Steelers_In_DC · ‎02-27-2015

It took me a while to get this data together and now it seems the clients display preference is going to be a huge undertaking. I'm not sure if transpose will put this together easily or if there is another way to get this but here it goes, below is a small portion of a dataset, power_id is a unique identifier and it's sorted by power_id record_date. Here is what I have: POWER_ID RECORD_DATE pid_default_reason cure_count ACCT_DISCLOSURE_STATUS_FLG PD_CD DEL_DAYS ACCT_CURRENT_CHARGE_OFF_AMT pass_date 0000196162 06/30/2013 3 P ** 0 0.00 0000196162 07/31/2013 4 P ** 0 0.00 0000196162 08/31/2013 5 P ** 0 0.00 0000196162 09/30/2013 6 P ** 0 0.00 09/30/2013 I've attached what I want because it didn't look very nice pasted. I had to use a csv because the excel format was too large. You'll notice there are different columns before and after September 2013. Any help will be appreciated.

Steelers_In_DC · ‎02-13-2015

I'm not sure I understand your solution. This is for a table that has millions of power_id's and facility_id's.

Steelers_In_DC · ‎02-13-2015

I am using a counter like this: data test; set data; by power_id record_date; cure_count+1; if first.power_id then count= 1; if default ne '' then count= 0; There is a facility_id that roles up to power_id. Is there a way to keep facility_id in this dataset but not count by it? Here is what I have: power_id facility_id record_date cure_count 1 1 1/1/2001 1 1 2 1/1/2001 2 1 3 1/1/2001 3 1 1 2/1/2001 4 1 2 2/1/2001 5 1 3 2/1/2001 6 Here is what I want: power_id facility_id record_date cure_count 1 1 1/1/2001 1 1 2 1/1/2001 1 1 3 1/1/2001 1 1 1 2/1/2001 2 1 2 2/1/2001 2 1 3 2/1/2001 2

Steelers_In_DC · ‎01-23-2015

Very nice solution. Thank you very much.

Steelers_In_DC · ‎01-22-2015

Hai.kuo, After 6 months of no default events the pass date is generated. If there is another default event at or after that time the default date is reset and retained from that date unless there is another pass date and default event following. If you look at line 9 and 25 of our results side by side you will see examples of what I am talking about.

Steelers_In_DC · ‎01-22-2015

Below is a solution. Anyone is welcome to poke holes in it or recommend a more creative way to come up with the same solution. This works but seems remedial to me: data data; infile cards dsd; informat record_date mmddyy10.; format record_date mmddyy10.; input power_id $ record_date default_reason $; cards; test1,8/31/2013, test1,9/30/2013,PD test1,10/31/2013, test1,11/30/2013, test1,12/31/2013, test1,1/31/2014, test1,2/28/2014, test1,3/31/2014, test1,4/30/2014,PD test1,5/31/2014, test1,6/30/2014, test1,7/31/2014, test1,8/31/2014, test1,9/30/2014,pd test1,10/31/2014,pd test1,11/30/2014, test1,12/31/2014, test1,1/31/2015, test1,2/28/2015, test1,3/31/2015, test1,4/30/2015, test1,5/31/2015, test1,6/30/2015, test1,7/31/2015, test1,8/31/2015,pd test1,9/30/2015, test1,10/31/2015, test1,11/30/2015, test1,12/31/2015, test1,1/31/2016, test1,2/29/2016, test1,3/31/2016, test2,8/31/2013,PD test2,9/30/2013,PD test2,10/31/2013, test2,11/30/2013, test2,12/31/2013, test2,1/31/2014, test2,2/28/2014,PD test2,3/31/2014, test2,4/30/2014, test2,5/31/2014, test2,6/30/2014, test2,7/31/2014, test2,8/31/2014, test2,9/30/2014, test2,10/31/2014,pd test2,11/30/2014, test2,12/31/2014, test2,1/31/2015, test2,2/28/2015,pd test2,3/31/2015,pd test2,4/30/2015, test2,5/31/2015, test2,6/30/2015, test2,7/31/2015, test2,8/31/2015, test2,9/30/2015, test2,10/31/2015, test2,11/30/2015, test2,12/31/2015, test2,1/31/2016,pd test2,2/29/2016, ; run; data have(keep=power_id record_date default_reason pass_date pass_count first_default_flag lpass_count); format pass_date mmddyy10.; retain pass_date first_default_flag; set data; by power_id; pass_count + 1; /*if first.power_id or default_reason ne '' then pass_date = .;*/ if first.power_id then pass_count = 0; /*account for records that do not start in default*/ if first.power_id and default_reason = '' then first_default_flag = 0; else if default_reason ne '' then first_default_flag = 1; if first_default_flag = 1 then do; if first.power_id then pass_date = .; if first.power_id then pass_count = 0; if default_reason ne '' then pass_count = 0; /* if pass_count = 0 then pass_date = .;*/ end; lpass_count = lag(pass_count); if lpass_count = 6 then pass_date = record_date; run; data want; format default_date mmddyy10.; retain default_date; set have; by power_id; pidcount +1; default_count +1; if default_reason = '' then default_count = 0; if first.power_id then pidcount = 1; if first.power_id and not missing(default_reason) then default_date = record_date; if not first.power_id then do; if default_count = 1 and lpass_count > 5 then default_date = record_date; end; if default_count = 1 and pass_count = 0 and lpass_count = 0 and not missing(default_reason) then default_date = record_date; run; proc sql; create table default_date as select power_id, record_date, default_reason, default_date, pass_date from want order by power_id, record_date;

Steelers_In_DC · ‎01-22-2015

I'm not sure it's a problem with the informat. It's a change in the way the file was formatted. You'll have to translate excel date to sas date. If you can put today() in for the date that would work nicely. Depending on frequency and amount of data this might not be a good option but it could be a start. data data2; infile cards; informat date1 mmddyy10.; input date1; cards; 04/19/1974 ; run; data have2; set data2; number = intck('day',date1,'18mar2015'd); run; data want2; format new_date mmddyy10.; set have; new_date = date1 + number; run;

Steelers_In_DC · ‎01-22-2015

I am trying to identify when a record first goes into default and retain that date as a default date. If the record comes out of default and then goes back into default that would reset the default date. I cannot figure out how to make this happen. Below are have and want datasets with my progress up to this point. Thank You, data data; infile cards dsd dlm=','; informat record_date mmddyy10.; format record_date mmddyy10.; input power_id $ record_date default_reason $; cards; test1,8/31/2013, test1,9/30/2013,PD test1,10/31/2013,PD test1,11/30/2013,PD test1,12/31/2013, test1,1/31/2014, test1,2/28/2014, test1,3/31/2014,PD test1,4/30/2014,PD test1,5/31/2014, test1,6/30/2014, test1,7/31/2014, test1,8/31/2014, test1,9/30/2014, test1,10/31/2014, test1,11/30/2014, test1,12/31/2014, test1,1/31/2015, test2,2/28/2015,PD test2,3/31/2015,PD test2,4/30/2015, test2,5/31/2015, test2,6/30/2015, test2,7/31/2015, test2,8/31/2015, test2,9/30/2015, test2,10/31/2015, test2,11/30/2015, test2,12/31/2015,PD test2,1/31/2016,PD test2,2/29/2016, test2,3/31/2016, test2,4/30/2016, test2,5/31/2016, test2,6/30/2016, ; run; data have(keep=power_id record_date default_reason pass_date pass_count first_default_flag); format pass_date mmddyy10.; retain pass_date first_default_flag; set data; by power_id; pass_count + 1; if first.power_id or default_reason ne '' then pass_date = .; if first.power_id then pass_count = 0; /*account for records that do not start in default*/ if first.power_id and default_reason = '' then first_default_flag = 0; else if default_reason ne '' then first_default_flag = 1; if first_default_flag = 1 then do; if first.power_id then pass_date = .; if first.power_id then pass_count = 0; if default_reason ne '' then pass_count = 0; if pass_count = 7 then pass_date = record_date; if pass_count > 6 and default_reason ne '' then pass_date = .; end; run; data want; infile cards dsd; informat record_date pass_date default_date mmddyy10.; format record_date pass_date default_date mmddyy10.; input power_id $ record_date default_reason $ pass_date default_date first_default_flag pass_count; cards; test1,08/31/2013,,,,0,0 test1,09/30/2013,PD,,09/30/2013,1,0 test1,10/31/2013,PD,,09/30/2013,1,0 test1,11/30/2013,PD,,09/30/2013,1,0 test1,12/31/2013,,,09/30/2013,1,1 test1,01/31/2014,,,09/30/2013,1,2 test1,02/28/2014,,,09/30/2013,1,3 test1,03/31/2014,PD,,09/30/2013,1,0 test1,04/30/2014,PD,,09/30/2013,1,0 test1,05/31/2014,,,09/30/2013,1,1 test1,06/30/2014,,,09/30/2013,1,2 test1,07/31/2014,,,09/30/2013,1,3 test1,08/31/2014,,,09/30/2013,1,4 test1,09/30/2014,,,09/30/2013,1,5 test1,10/31/2014,,,09/30/2013,1,6 test1,11/30/2014,,11/30/2014,09/30/2013,1,7 test1,12/31/2014,,11/30/2014,09/30/2013,1,8 test1,01/31/2015,,11/30/2014,09/30/2013,1,9 test2,02/28/2015,PD,,02/28/2015,1,0 test2,03/31/2015,PD,,02/28/2015,1,0 test2,04/30/2015,,,02/28/2015,1,1 test2,05/31/2015,,,02/28/2015,1,2 test2,06/30/2015,,,02/28/2015,1,3 test2,07/31/2015,,,02/28/2015,1,4 test2,08/31/2015,,,02/28/2015,1,5 test2,09/30/2015,,,02/28/2015,1,6 test2,10/31/2015,,10/31/2015,02/28/2015,1,7 test2,11/30/2015,,10/31/2015,02/28/2015,1,8 test2,12/31/2015,PD,,12/31/2015,1,0 test2,01/31/2016,PD,,12/31/2015,1,0 test2,02/29/2016,,,12/31/2015,1,1 test2,03/31/2016,,,12/31/2015,1,2 test2,04/30/2016,,,12/31/2015,1,3 test2,05/31/2016,,,12/31/2015,1,4 test2,06/30/2016,,,12/31/2015,1,5 ; run;

Steelers_In_DC · ‎01-08-2015

I have done the following to accomplish the same thing. I'm guessing there is a better way but I know this will work. proc datasets lib='libname' nolist;contents data=_all_ noprint out='new_dataset';quit;run; proc sql; create table table_names as select DISTINCT MEMNAME from 'new_dataset'; This will give you all of the table names, copy and past them to get something like this: data want; set table1 table2 table3 table...; run;

Steelers_In_DC · ‎01-08-2015

I changed the code around a little for the exact result I want, this works great, I appreciate the extra explanation you add. That was a big help: data want; do until (last.power_id); set complete_sort; by power_id; count+1; if first.power_id then count = 1; end; do until (last.power_id); set complete_sort; by power_id; if count > 1 then output; end; /* where pass_date <> . and '30aug2012'd <= default_date_end <= '01jul2013'd;*/ run;

Steelers_In_DC · ‎01-08-2015

Awesome, thanks.

Steelers_In_DC · ‎01-08-2015

I know how to do it in sql, I'm trying to get a better understanding of multiple set statements and haven't any good literature for this example online. Thanks though,

Steelers_In_DC · ‎01-08-2015

Tpham, you will want to use a put function: proc format; value $namefmt 1="Experiment" 2="Control"; run; data have; Input name $ count; datalines; 1 16 2 8 ; run; data want; format name $ namefmt.; set have; wantvar= catx('',put(name,namefmt.),'(N=',count,')'); run;

Steelers_In_DC · ‎01-08-2015

have id count 1 1 1 2 1 3 2 1 2 2 3 1 4 1 5 1 want id count 1 1 1 2 1 3 2 1 2 2

Online Status	Offline
Date Last Visited	‎07-12-2025 12:12 PM

sql server using 'with'

Re: hive show table and column

hive show table and column

proc sql using or

find edge node

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Fill in rows with previous value if next value is the same.

Re: reg ex extract

Re: proc sql using or

Re: Adding quotes to a Macro Variable

Re: How to run a program in EG5.1 without connecting to a server

Re: macro variable in set statements based on today()

Finding all single linked chains: the allChains macro

find edge node

infile 'red x' with no error in log

Re: Extracting Month, Year and quarter

Re: Which Date function can be used to extract month(Character) from t...

Re: How to assign a column value based on another column ??

Re: Proc transpose, possibly other solution

Proc transpose, possibly other solution

Re: set counter

set counter

Re: retain date when first instance of default

Re: retain date when first instance of default

Re: retain date when first instance of default

Re: Date informat MMDDYY10. incorrect?

retain date when first instance of default

Re: How to append all dataset in one library

Re: multiple set statement for self join

Re: multiple set statement for self join

Re: multiple set statement for self join

Re: How do I convert a format into a string value?

Re: multiple set statement for self join