CODEWITHSUNDEEP
macrossas
DukeLuke
DukeLuke

Reputation: 315

SAS - Reading value from one observation into a another observation

I'm using SAS and I'm trying to read an observation's value from a previous observation into a current observation.

here's what the data looks like

Obs URN       Description Error_Bucket inputAcctNumber             count 
1 010001234567 Base      Invalid Name  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 1 
2 010001234567 No Error  No Error          0                          2 
3 010007891023 No Error  No Error      BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB 1 
4 010007891023 A2/J2     Invalid Name      0                          2 
5 010004567890 No Error  No Error      CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 1 
6 010004567890 A2/J2     Invalid Name      0                          2 
7 010001354321 No Error  No Error      DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD 1

I have created the Count field and in this example there are only two observations with the same URN, but in the future there could be 3 - N number of observations with the same URN number. What I'm trying to do is give all of these observations the same InputAcctNumber that have the same URN number, but right now they are all 0's. How can I do this, especially if the number of "counts" with the urn are ambiguous.

Ideally this is what I want my data to look like:

    Obs URN       Description Error_Bucket inputAcctNumber             count 
1 010001234567 Base      Invalid Name  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 1 
2 010001234567 No Error  No Error      AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2 
3 010007891023 No Error  No Error      BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB 1 
4 010007891023 A2/J2     Invalid Name  BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB 2 
5 010004567890 No Error  No Error      CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 1 
6 010004567890 A2/J2     Invalid Name  CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2 
7 010001354321 No Error  No Error      DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD 1

Upvotes: 1

Views: 295

Answers (3)

david25272
david25272

Reputation: 974

You can use the retain statement to get SAS to retain a previous records value in a datastep.

Assuming the data can be sorted so that records with valid inputAcctNumber occur first, you can use code like this:

proc sort; by URN count;

data test_output (drop=replacement);
 length replacement $50;
 retain replacement;
 set test_input;
 if trim(inputAcctNumber) = '0' then inputAcctNumber=replacement;
 output;
 if trim(inputAcctNumber) ne '0' then replacement=inputAcctNumber;
run;

Upvotes: 0

Tom
Tom

Reputation: 51611

A simple way is just to merge the inputAcctNumber variable back onto the data.

data want ;
  merge have (drop=inputAcctNumber )
        have (keep=URN inputAcctNumber where=(inputAcctNumber ne '0'))
  ;
  by URN;
run;

If the first record always has the right number then you could make a new variable and retain the value.

data want;
  set have ;
  by urn count ;
  if first.urn then new=inputAcctNumber;
  retain new;
  drop inputAcctNumber;
  rename new=inputAcctNumber;
run;

Upvotes: 2

Arnold J Rimmer
Arnold J Rimmer

Reputation: 13

I've come up with a way to do what you asked, using PROC SQL. I do this by creating 4 different tables, with the last one being the one you wanted. This may not be the most efficient way for you to do this, but you know your data better than I do.

Code:

proc sql noprint; 
  create table b as 
    select * 
      from a
        where count eq 1;

  create table c as
    select * 
      from a 
        where count ne 1;

  create table d as
    select c.URN, c.Description, c.Error_Bucket, b.inputAcctNumber, c.count
      from b b inner join c c
        on b.urn=c.URN
          order by URN;

  create table e as
    select * 
      from b
      outer union corresponding
    select *
      from c
        order by URN; 
quit;

Output with a proc print of dataset 'e':

Obs URN Description Error_Bucket inputAcctNumber count 
1 10001234567 No Error No Error  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2 
2 10001234567 Base Invalid Name  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 1 
3 10001354321 No Error No Error  DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD 1 
4 10004567890 No Error No Error  CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 1 
5 10004567890 A2/J2 Invalid Name CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2 
6 10007891023 A2/J2 Invalid Name BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB 2 
7 10007891023 No Error No Error  BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB 1

Full Explanation:

(1) I made a table of rows with 'count' equal to 1. (2) I made another table where 'count' is not equal to 1. (3) I then did an inner join of the table made in (2) on the one made in (1) with 'inputAcctNumber' from table made in (1) instead of the one made in (2). (4) I then appended the table made in (3) to the one made in (1) and then sorted by 'URN'.

Hopefully this is helpful.

Upvotes: 0

Related Questions