CODEWITHSUNDEEP
sasenterprise-guide
user3681328
user3681328

Reputation: 5

SAS retrieving values from row

I want to retrieve row level values (Loans associated with account number) from a SAS table - Please find below example.

enter image description here

Input 

Account Number  Loans
123             abc, def, ghi
456             jkl, mnopqr, stuv
789             w, xyz

Output 

Account Numbers Loans
123             abc
123             def
123             ghi
456             jkl
456             mnopqr
456             stuv
789             w
789             xyz

Loans are separated by commas and they don't have fix length.

Upvotes: 0

Views: 101

Answers (1)

Dirk Horsten
Dirk Horsten

Reputation: 3845

Use countw() to count the number of values on a line and scan() to pick them out.

Both have a last optional variable to specify the separator, which in your case is ,.

data Loans (keep= AccountNo Loan);
    infile datalines truncover;
    Input @1 AccountNo 3. @17 LoanList $250.;
    if length(LoanList) gt 240 then put 'WARNING: You might need to extend Loans';

    label AccountNo = 'Account Number' Loan = 'Loans';
    do loanNo = 1 to countw(LoanList, ',');
        Loan = scan(LoanList, loanNo, ',');
        output;
    end;
    datalines;
123             abc, def, ghi
456             jkl, mnopqr, stuv
789             w, xyz
;

proc print data=Loans label noobs;
run;

The reverse operation requires different techniques.

To enable by AccountNo processing, we must first construct a SAS dataset from the input and then read that back in with a set statement. 

data Loans;
    infile datalines;
    input @1 AccountNo 3. @5 Loan $25.;
    datalines;
123 15-abc 
123 15-def 
123 15-ghi 
456 99-jkl 
456 99-mnopqr 
456 99-stuv 
789 77-w 
789 77-xyz
;
data LoanLists;
    set Loans;
    by AccountNo;

Now create your Loanlist long enough and overwrite the default behaviour of SAS to re-initialise all variables for every observation (=row of data).

    format Loanlist $250.;
    retain Loanlist;

Collect all loans for an account, separating them with comma an blank.

    if first.AccountNo then Loanlist = Loan;
    else Loanlist = catx(', ',Loanlist,Loan);
    if length(LoanList) gt 240 then put 'WARNING: you might need to extend LoanList';

Keep only the full list per account.

    if last.AccountNo;
    drop Loan;
proc print;
run;

Upvotes: 1

Related Questions