Learning SAS? Welcome to the exclusive online community for all SAS learners.

Excel cell with multiple entries

Reply
Contributor
Posts: 40

Excel cell with multiple entries

 

I have an excel sheet which has more than one entry for some variables for a given obervation.It looks something like this:

 

IDAll Managers
Amount
123ABC
XYZ
PQR
213
24
342
124ABC
XYZ
PQR
204
156
5436

 

 

 

There are no delimiters either. When I read it from sas it also looks exactly like the table above.

 

But I want sas to read it like the table given below ( where the multiple entries are separated in to rows):

 

 

IDAll ManagersAmount
123ABC213
123XYZ24
123PQR342
124ABC204
124XYZ156
124PQR5436

 

I'm new to programming so any help would be much appreciated! Thanks!

Super User
Posts: 19,869

Re: Excel cell with multiple entries

SAS can't read it by default. You can parse it out however, look at the SCAN function.

Do all your data have 3 values or do they vary?

 

This is pretty much your question. You'll need to do have two functions in the loop though, 1 for the name and 1 for the value.

 

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;

 

http://stackoverflow.com/questions/37903812/sas-retrieving-values-from-row

Contributor
Posts: 40

Re: Excel cell with multiple entries

Thanks, I manged to sort it out on excel itself. So it made things easier!

Ask a Question
Discussion stats
  • 2 replies
  • 312 views
  • 1 like
  • 2 in conversation