Array with missing data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Array with missing data

 

Hi everyone, the purpose is to reshape data long to wide. Below is the code i am currently working on. However, because some accounts have missing data, array does not give what i want. For example, account 333 reason 2 & reason 3 should be populated as blank however now the value is populated since reason 4 & 5 move up.

 

Anyone would be able to help with my code? Thanks in advance.

 

HAVE                                    
  201505 201504 201503 201502 201501 201412 201505 201504 201503 201502 201501 201412 201505 201504 201503 201502 201501 201412
acct_num reason1 reason2 reason3 reason4 reason5 reason6 bal1 bal2 bal3 bal4 bal5 bal6 prod1 prod2 prod3 prod4 prod5 prod6
111 Y Y N N Y Y 100 90 125 0 25 10 2 2 4 1 6 3
222 N Y N . . . 0 25 10 . . . 8 12 35 . . .
333 Y N Y Y . . 75 65 75 45 . . 28 15 30 60 . .
                                     
WANT                                    
acct_num reason1 reason2 reason3 reason4 reason5 reason6 bal1 bal2 bal3 bal4 bal5 bal6 prod1 prod2 prod3 prod4 prod5 prod6
111 Y Y N N Y Y 100 90 125 0 25 10 2 2 4 1 6 3
222       N Y N       0 25 10       8 12 35
333 Y     N Y Y 75     65 75 45 28     15 30 60

 

data have;

input acct_num date reason $11-12 Bal Prod;

cards;

111 201505 Y 100 2

111 201504 Y 90 2

111 201503 N 125 4

111 201502 N 0 1

111 201501 Y 25 6

111 201412 Y 10 3

222 201502 N 0 8

222 201501 Y 25 12

222 201412 N 10 35

333 201505 Y 75 28

333 201502 N 65 15

333 201501 Y 75 30

333 201412 Y 45 60

;

run;

DATA want (DROP=i date reason bal prod);

SET have;

BY acct_num descending date;

ARRAY reason_array {6} $ reason1-reason6;

ARRAY Bal_array {6} Bal1-Bal6;

ARRAY Prod_array {6} Prod1-Prod6;

 

RETAIN i

reason1-reason6

Bal1-Bal6

Prod1-Prod6

;

IF FIRST.acct_num then do;

DO i = 1 to 6;

reason_array(i) =.;

bal_array(i) =.;

prod_array(i) =.;

 

END;

i = 1;

END;

reason_array(i) =reason;

bal_array(i) =bal;

prod_array(i) =prod;

i = i + 1;

IF last.acct_num;

RUN;

 


Accepted Solutions
Solution
‎06-13-2016 10:18 AM
Super User
Posts: 10,552

Re: Array with missing data

[ Edited ]

Since your input data for acct 333 has no values associated with 201504 or 201503 how is the program supposed to know that it needs to iterate the counter but not assign values until you get to 201502? Similar the 222 account and 201505 to 201503 data.

 

If you use the date information as dates you can get a shift from a given date to assign the index into the array directly and solve that issue.

 

data have;
input acct_num date yymmn6. reason $11-12 Bal Prod;
format date yymmn6.;
cards;
111 201505 Y 100 2
111 201504 Y 90 2
111 201503 N 125 4
111 201502 N 0 1
111 201501 Y 25 6
111 201412 Y 10 3
222 201502 N 0 8
222 201501 Y 25 12
222 201412 N 10 35
333 201505 Y 75 28
333 201502 N 65 15
333 201501 Y 75 30
333 201412 Y 45 60
;
run;
DATA want (DROP=i date reason bal prod);
   SET have; 
   BY acct_num descending date;
   ARRAY reason_array {6} $ reason1-reason6;
   ARRAY Bal_array {6} Bal1-Bal6;
   ARRAY Prod_array {6} Prod1-Prod6;
   basedate= '01MAY2015'd; /* the latest "column", note date literals must be in this form*/
                           /* also the default when reading with the YYMMN format is the first of the month*/
   RETAIN  
      reason1-reason6
      Bal1-Bal6
      Prod1-Prod6
   ;
   IF FIRST.acct_num then do;
      DO i = 1 to 6; 
       reason_array(i) ="";
       bal_array(i) =.;
       prod_array(i) =.;
       
      END;
       
   END;
   index = intck('month',date,basedate)+1;
   reason_array(index) =reason;
   bal_array(index) =bal;
   prod_array(index) =prod;
   IF last.acct_num;
RUN;

 

View solution in original post


All Replies
Solution
‎06-13-2016 10:18 AM
Super User
Posts: 10,552

Re: Array with missing data

[ Edited ]

Since your input data for acct 333 has no values associated with 201504 or 201503 how is the program supposed to know that it needs to iterate the counter but not assign values until you get to 201502? Similar the 222 account and 201505 to 201503 data.

 

If you use the date information as dates you can get a shift from a given date to assign the index into the array directly and solve that issue.

 

data have;
input acct_num date yymmn6. reason $11-12 Bal Prod;
format date yymmn6.;
cards;
111 201505 Y 100 2
111 201504 Y 90 2
111 201503 N 125 4
111 201502 N 0 1
111 201501 Y 25 6
111 201412 Y 10 3
222 201502 N 0 8
222 201501 Y 25 12
222 201412 N 10 35
333 201505 Y 75 28
333 201502 N 65 15
333 201501 Y 75 30
333 201412 Y 45 60
;
run;
DATA want (DROP=i date reason bal prod);
   SET have; 
   BY acct_num descending date;
   ARRAY reason_array {6} $ reason1-reason6;
   ARRAY Bal_array {6} Bal1-Bal6;
   ARRAY Prod_array {6} Prod1-Prod6;
   basedate= '01MAY2015'd; /* the latest "column", note date literals must be in this form*/
                           /* also the default when reading with the YYMMN format is the first of the month*/
   RETAIN  
      reason1-reason6
      Bal1-Bal6
      Prod1-Prod6
   ;
   IF FIRST.acct_num then do;
      DO i = 1 to 6; 
       reason_array(i) ="";
       bal_array(i) =.;
       prod_array(i) =.;
       
      END;
       
   END;
   index = intck('month',date,basedate)+1;
   reason_array(index) =reason;
   bal_array(index) =bal;
   prod_array(index) =prod;
   IF last.acct_num;
RUN;

 

Respected Advisor
Posts: 4,663

Re: Array with missing data

Here goes to a cumbersome data structure Smiley Happy:

 

data have;
input acct_num date :yymmn6. reason :$1. Bal Prod;
format date yymmdd.;
cards;
111 201505 Y 100 2
111 201504 Y 90 2
111 201503 N 125 4
111 201502 N 0 1
111 201501 Y 25 6
111 201412 Y 10 3
222 201502 N 0 8
222 201501 Y 25 12
222 201412 N 10 35
333 201505 Y 75 28
333 201502 N 65 15
333 201501 Y 75 30
333 201412 Y 45 60
;

proc sql;
create table temp as
select *, 
    intck("MONTH", date, max(date)) + 1 as r
from have
order by acct_num, date desc;
select max(r) into :maxr trimmed from temp;
quit;

data want;
array _reason $1 reason1-reason&maxr;
array _bal bal1-bal&maxr;
array _prod prod1-prod&maxr;
do until (last.acct_num);
    set temp; by acct_num;
    _reason{r} = reason;
    _bal{r} = Bal;
    _Prod{r} = Prod;
    end;
drop date reason bal prod r;
run;




PG
Super User
Posts: 9,691

Re: Array with missing data

Why not proc transpose since you have an ID variable ?

 

data have;
input acct_num date reason $11-12 Bal Prod;
cards;
111 201505 Y 100 2
111 201504 Y 90 2
111 201503 N 125 4
111 201502 N 0 1
111 201501 Y 25 6
111 201412 Y 10 3
222 201502 N 0 8
222 201501 Y 25 12
222 201412 N 10 35
333 201505 Y 75 28
333 201502 N 65 15
333 201501 Y 75 30
333 201412 Y 45 60
;
run;
proc transpose data=have out=temp1 prefix=reason_ ;
by acct_num ;
var reason ;
id date ;
run;
proc transpose data=have out=temp2 prefix=Bal_;
by acct_num ;
var Bal ;
id date ;
run;
proc transpose data=have out=temp3 prefix=Prod_;
by acct_num ;
var Prod;
id date ;
run;
data want;
 merge temp1-temp3;
 by acct_num ;
 drop _name_;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 250 views
  • 3 likes
  • 4 in conversation