DATA Step, Macro, Functions and more

Long to wide Array

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Long to wide Array

     I am having some trouble with the following array data step.  With my limited understanding of Arrays and SAS programing I thought that i could use an arrays variable name to locate data into the array.  I cant seem to google anything that explains this very well.  In this data step I am trying to use the Usage_type that is equal to the array variable names to change the data from long to wide.  I get an error in the log for "Invalid numeric data, usage_type='AA'.  Do i have to call out the usage type as a character some how?   I know i could use proc transpose but i really need to start using arrays more.  Can anyone tell me why this doesn't work.

Thank you so much.

Data temp_ytd_cost_v;

     array ytdcv{4} US OH AA RM;

     do until (last.apg1);

          set temp_ytd_cost_v;

          by customer gd apg1;   

          ytdcv{usage_type}=costV

     end;

Run;

data type: usage_type = char, same as variable names of the array


Accepted Solutions
Solution
‎05-14-2014 12:14 PM
Contributor
Posts: 53

Re: Long to wide Array

I will need it in heat map type of report eventually (if that possible in sas)  but my knowledge of sas has not made it there yet.  Thanks to all the help here I just managed to get the following code to produce the output I am looking for minus any formatting.  Any suggested improvements to this code or a report output would be welcome. 

Thanks

data temp_YTD_Cost_V(drop=_utype costv trans_cost ytdaco usage_type);

    array ytdcv{5} $ US OH AA RM Total;

    do until (last.apg1);

        Set temp_YTD_Cost_V;

        by customer gd apg1;

            if usage_type='US' then _utype=1;

            if usage_type='OH' then _utype=2;

            if usage_type='AA' then _utype=3;

            if usage_type='RM' then _utype=4;

        ytdcv{_utype}=costV;

    end;

   

    ytdcv{5}=sum(ytdcv{1},ytdcv{2},ytdcv{3},ytdcv{4});

       

run;

View solution in original post


All Replies
Super User
Posts: 17,861

Re: Long to wide Array

Try the following modification.

Data temp_ytd_cost_v;

     array ytdcv{4} $ ( "US" "OH" "AA" "RM");

     do until (last.apg1);

          set temp_ytd_cost_v;

          by customer gd apg1;   

          ytdcv{usage_type}=costV

     end;

Run;

Super User
Super User
Posts: 7,407

Re: Long to wide Array

Your logic doesn't seem to make sense.  The do loop is using last.apg1 as its condition, now last. is a per observation flag, the do loop is operating within an observation.  Secondly why is the set statement within the do loop?

Please post some test data and how you would like it to look so I can se what you are trying to achieve.

Contributor
Posts: 53

Re: Long to wide Array

In response to RW9's question

Data table in is listed below.  The set statement causes me to iterate to a new observation every time it is read.  This allows me to move through the records and populate the array without writing anything to my new record set until i am ready.  Once i hit the last.apg1 i exit the loop and write the observation to my new record set

Data In

CustomergdAPG1usage_typeCostV
ZXAAA$600
ZXARM$200
ZXAUS$300
ZXBAA$5,000
ZXBOH$2,000
ZXBRM$1,200
ZXBUS$400
ZXCAA$30
ZXCRM$50
ZXCUS$60

Data out needed


CustomerGDAPG1           AA    OH       RM   US
ZX   A$600      .$200$300
ZX    B$5,000$2,000$1,200$400
ZX   C$30      .$50$60
Super User
Super User
Posts: 7,407

Re: Long to wide Array

TBH, I don't think I would use array processing for this.  I realize you want to learn arrays and all, but this really is why there is a function transpose.  There are also other methods to get to what you want:

data want;
merge  have (keep=customer ge apg1 rename=(costv=aa) where=(usage_type="AA"))
  have (keep=customer ge apg1 rename=(costv=oh) where=(usage_type="OH"))
  have (keep=customer ge apg1 rename=(costv=rm) where=(usage_type="RM"))
  have (keep=customer ge apg1 rename=(costv=us) where=(usage_type="US"));
by customer ge apg1;
run;

data want;
attrib aa oh rm us format=best.;
set have;
by apg1;
if usage_type="AA" then aa=costv;
if usage_type="OH" then OH=costv;
if usage_type="RM" then RM=costv;
if usage_type="US" then US=costv;
if last.apg1 then output;
run;

proc transpose data=have out=want;
by customer ge apg1;
var costv;
id usage_type;
idlabel usage_type;
run;

Respected Advisor
Posts: 3,777

Re: Long to wide Array

It doesn't work because an array index, the part between the {}, can't be a character.

You need to figure out a way to convert usage_type into a number that corresponds to variable location in the array. 

Does USAGE_TYPE have the same value as the array element variable names you show US OH AA RM?

Super User
Posts: 10,516

Re: Long to wide Array

Do you need a dataset or a report with that layout?

Solution
‎05-14-2014 12:14 PM
Contributor
Posts: 53

Re: Long to wide Array

I will need it in heat map type of report eventually (if that possible in sas)  but my knowledge of sas has not made it there yet.  Thanks to all the help here I just managed to get the following code to produce the output I am looking for minus any formatting.  Any suggested improvements to this code or a report output would be welcome. 

Thanks

data temp_YTD_Cost_V(drop=_utype costv trans_cost ytdaco usage_type);

    array ytdcv{5} $ US OH AA RM Total;

    do until (last.apg1);

        Set temp_YTD_Cost_V;

        by customer gd apg1;

            if usage_type='US' then _utype=1;

            if usage_type='OH' then _utype=2;

            if usage_type='AA' then _utype=3;

            if usage_type='RM' then _utype=4;

        ytdcv{_utype}=costV;

    end;

   

    ytdcv{5}=sum(ytdcv{1},ytdcv{2},ytdcv{3},ytdcv{4});

       

run;

Regular Contributor
Posts: 217

Re: Long to wide Array

dsbihill,

Thank you for posting your code.  I have not witnessed your approach before.  I would recommend defaulting the array member values to zero.  Some SAS functions fail when adding missing values.

/* I did not test this  but I have seen this style */

array ytdcv{5} $ US OH AA RM Total 5*0;

or

/* I have also seen this style */

if first.apg1 then do;

do z=1 to 5;

     ytdcv(z) = 0;

end;

end;


☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 349 views
  • 6 likes
  • 6 in conversation