BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dsbihill
Obsidian | Level 7

     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

1 ACCEPTED SOLUTION

Accepted Solutions
dsbihill
Obsidian | Level 7

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

9 REPLIES 9
Reeza
Super User

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

dsbihill
Obsidian | Level 7

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

data_null__
Jade | Level 19

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?

ballardw
Super User

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

dsbihill
Obsidian | Level 7

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;

jwillis
Quartz | Level 8

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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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