topic Re: How to manage this type of file? Please Help. Many Thanks in SAS Programming

How to manage this type of file? Please Help. Many Thanks

Question — Thu, 25 Apr 2013 13:40:13 GMT

Hi All,

I would like to match the file attached to anothe file on account_number. This file contains a column called 'Account Number' and another column called 'Order delivery date. But the account number is only populated for the first order. For example if a customer has made 3 orders, the first account number will be populated but the second and third row is missing for account number but it should be the same account number. (We should have duplicates instead of missing).

I have received this file in this format and unfortunately I can't do it manually, It will take ages. I would like to match this file to another file to get the 'Order delivery date, but I will have to sort on account number but I wouldn't know which account number to associate to the missing values. I am not sure if you can see the file attached. If not, please let me know because I am having problems with attachement.

Many Thanks

Account Number	Order Delivery date
100000001	01/01/2012
I should Have here 100000001 instead of missing	05/01/2012
I should Have here 100000001 instead of missing	10/01/2012
10000002	01/01/2012
10000003	20/01/2013
10000004	15/02/2013
I should Have here 100000004 instead of missing	18/02/2013
I should Have here 100000004 instead of missing	20/02/2013
10000005	15/04/2013
I should Have here 100000005 instead of missing	18/04/2013

Re: How to manage this type of file? Please Help. Many Thanks

AncaTilea — Thu, 25 Apr 2013 13:57:12 GMT

Hi.

How about this:

data want;

set your_data;

retain temp;

if accnt_num ne . then temp = accnt_num;

else if accnt_num =. then accnt_num = temp;

run;

Good luck!

Anca.

Re: How to manage this type of file? Please Help. Many Thanks

Question — Thu, 25 Apr 2013 14:30:58 GMT

Thank You Very Much...

Re: How to manage this type of file? Please Help. Many Thanks

Aad — Fri, 26 Apr 2013 06:52:26 GMT

A variation on the above solution which does not use IF statements anymore:

Data WANT (Drop = TEMP);

Set YOUR_DATA;

Retain TEMP;

TEMP = Coalesce (ACCNT_NUM, TEMP);

ACCNT_NUM = TEMP;
RUN;

NOTE: If the ACCNT_NUM, though the name sugests otherwise, is a CHARACTER variable, you should replace the Coalesce woth a CoalesceC