DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 96

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

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 NumberOrder Delivery date
10000000101/01/2012
I should Have here 100000001 instead of missing05/01/2012
I should Have here 100000001 instead of missing10/01/2012
1000000201/01/2012
1000000320/01/2013
1000000415/02/2013
I should Have here 100000004 instead of missing18/02/2013
I should Have here 100000004 instead of missing20/02/2013
1000000515/04/2013
I should Have here 100000005 instead of missing18/04/2013
Super Contributor
Posts: 543

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

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.

Frequent Contributor
Posts: 96

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

Posted in reply to AncaTilea

Thank You Very Much...

Occasional Contributor Aad
Occasional Contributor
Posts: 8

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

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

Ask a Question
Discussion stats
  • 3 replies
  • 254 views
  • 1 like
  • 3 in conversation