topic Re: Run SAS array when array value is not consecutive in SAS Programming

Run SAS array when array value is not consecutive

Petergao1 — Tue, 14 Jul 2020 19:05:03 GMT

Hi friends,

I have a question to ask. When one runs an array job, the array's dimension-size

value must be consecutive, like from 5-9 (5,6,7,8,9). I have a data set for schools with subgroups. which is not consecutive. Its value is 1,4,5,6,7,8,9,13,14,25, in long format.

In order for the array to run,I have to create another variable, called group, to re-assign value to be consecutive, like, 1,2,3,4,5,6,7,8,9,10, (when subgroup=1, group=1, subgroup=4, group=2, subgroup=5, group=3, etc. Otherwise my array won't run properly.

This program is to turn data file from long to wide, to control the output, to avoid using a transpose, which is done so in the below codes.

Problem: After my data set was processed, I have to rename my output columns back to subgroup values, which is additional work and tedious.

My question: is there some way that one can use original data' s value (nonconsecutive) to run an array and get the job done?

*Note I have a data step to create a new var (group) before I can run my data step with an array;

*Also the file is sorted by sch_id and group;

Here is my program that is tested and working:

DATA want_wide ; *create a new data file name to be in wide format;
SET have_long; *read in the existing data file in long format;
By sch_id ; *read in data by the order of sch_id-original data each school has 10 rows (or less) of data;

KEEP sch_id EM_ELA_statusCY1 - EM_ELA_statusCY10; *keep output data's these column only;
RETAIN sch_id EM_ELA_statusCY1 - EM_ELA_statusCY10 ; *keep output data's column in this order;

ARRAY emstatus (1:10) $ EM_ELA_statusCY1 - EM_ELA_statusCY10 ; *process data by group value, from 1 to 10;
*based on sh_id, do the data step by the order of group id value;
IF first.sch_id THEN DO;
DO i = 1 to 10 ;
emstatus ( i ) = "" ; *output data is character type;
*array's order is based on i's value;
*above defaulted array index output to be missing (" "), when it has data,then it will be replaced;
END;*finish array execution;
END;* finish the data round beginning with sch_id do loop;
emstatus ( group ) = EM_ELA_statusCY ; *this is the column to be transposed;
* this will assign output new column name (EM subject=ELA) combined with group value for each new column;
IF last.sch_id THEN OUTPUT ;*when reached the last sch_id, output data;
RUN; *It worked, got the output as desired-long data set become wide, one school per row with 10 new columns;

* After this I have to rename the 10 columns back to the subgroup value, say

EM_ELA_statusCY8=EM_ELA_statusCY13,

EM_ELA_statusCY9= EM_ELA_statusCY14

EM_ELA_statusCY10=EM_ELA_statusCY25,etc.;

*Since I have 40 such columns, renaming them can be time consuming, the example only show 10 of them, there are three other sets of data, each set will produce 10 columns;

Any idea to go around so I do not need to create a new group var, and after the job rename my output columns back to subgroups' value?

Thank you.

Curious Peter

Re: Run SAS array when array value is not consecutive

Reeza — Tue, 14 Jul 2020 19:10:36 GMT

Why not use TRANSPOSE instead of avoiding it?

One of the key benefits of TRANSPOSE is the ID and IDLABEL statement which easily allow you to control the new variable names as well as the variable labels.

Re: Run SAS array when array value is not consecutive

PaigeMiller — Tue, 14 Jul 2020 19:19:54 GMT

I like @Reeza 's suggestion.

Alternatively:

If you have variables var1, var4, var5, var6, var7, var8, var9, var13, var14, var25, this should work (note the double-dash)

ARRAY emstatus $ var1--var25 ;

Also, I haven't gone through your code in detail, but usually converting long to wide just makes your problem worse. Usually long data sets are much easier to handle and analyze.

Re: Run SAS array when array value is not consecutive

Petergao1 — Tue, 14 Jul 2020 19:40:37 GMT

I have program that run the transpose job. Now I am seeking alternative way to have it done differently.

Re: Run SAS array when array value is not consecutive

Petergao1 — Tue, 14 Jul 2020 19:43:44 GMT

Thanks for the suggestion. My data is in long format and there is one column called subgroup with values of 1,4,5,6,7,8,9,13,14,25; another column is the Em ELA status. that is why and where I encounter problem if I use the subgroup values as my array's dimension.

Re: Run SAS array when array value is not consecutive

ballardw — Tue, 14 Jul 2020 19:48:06 GMT

Quite often it is a very good idea to provide example starting data, the desired output and the rules involved.

Transpose is likely an option.

Some ideas:

You have code like this to set all elements of an array to missing:

DO i = 1 to 10 ;
emstatus ( i ) = "" ; *output data is character type;
*array's order is based on i's value;
*above defaulted array index output to be missing (" "), when it has data,then it will be replaced;
END;*finish array execution;

The do loop is unneeded you can accomplish the exact same thing with:

call missing( of emstatus(*));

Call missing sets the values of all variables, and yes a mix of character and numeric is allowed, in the parentheses to missing for the variable type. You can process all elements of an array with the "of arrayname(*) " syntax.

One option might be to consider a custom custom format that would map your desired group values (still not quite sure why the word "group" is used ). Then you could use index values like varname[ functname(indexvalue)] to get the "group" .

But likely the introduction of additional variables to create the needed names with the ID statement in Proc Transpose is going to much easier.

Re: Run SAS array when array value is not consecutive

Reeza — Tue, 14 Jul 2020 20:12:35 GMT

Another option is to ignore the missing indexes. Create all the variables, even the empty ones. At the end, do a check for variables that are entirely empty and drop them.

Re: Run SAS array when array value is not consecutive

Petergao1 — Tue, 14 Jul 2020 20:52:47 GMT

Thanks again for the suggestion. I don't mind the array will run a dimension from 1, 2,3,... till 25. And then I can drop those empty cases. But I do not know how to do that.

Here is the data structure (I took the first one case) and the first status column to process it into wide format. Th out put will be one row for one school with additional 10 columns, each columns is a group's status for a subject, like ELA.

sch_id	groupid	group	EM_ELA_StatusCY	EM_Math_StatusCY	HS_ELA_StatusCY	HS_Math_StatusCY
1	1	1	GS:MP	GS:MP	-	-
1	4	2	-	-	-	-
1	5	3	-	-	-	-
1	6	4	-	-	-	-
1	7	5	-	-	-	-
1	8	6	-	-	-	-
1	9	7	GS:MP	GS:MP	-	-
1	13	8	-	-	-	-
1	14	9	GS:WAA	GS:WAA	-	-
1	25	10	-	-	-	-

Re: Run SAS array when array value is not consecutive

Reeza — Tue, 14 Jul 2020 21:31:33 GMT

If you do a quick search on dropping empty columns on here you'll find that it's a question that gets ask and answered at least once a month I'd guess.

Re: Run SAS array when array value is not consecutive

PaigeMiller — Tue, 14 Jul 2020 21:34:06 GMT

@Petergao1 wrote:

Thanks for the suggestion. My data is in long format and there is one column called subgroup with values of 1,4,5,6,7,8,9,13,14,25; another column is the Em ELA status. that is why and where I encounter problem if I use the subgroup values as my array's dimension.

Everything you said points to not transposing the data, leaving it long instead of wide. But you'd have to tell us what analysis you are planning with this data to be sure.

Also, if you TRANSPOSE the data, there shouldn't be any problems using the "double-dash" method I showed.

So I'm not sure what your resistance is to either idea.

Re: Run SAS array when array value is not consecutive

Reeza — Tue, 14 Jul 2020 22:07:29 GMT

I have program that run the transpose job. Now I am seeking alternative way to have it done differently.

Given that this seems like more of an academic/learning exercise.

Re: Run SAS array when array value is not consecutive

ballardw — Tue, 14 Jul 2020 22:27:25 GMT

@Petergao1 wrote:

Thanks again for the suggestion. I don't mind the array will run a dimension from 1, 2,3,... till 25. And then I can drop those empty cases. But I do not know how to do that.

Here is the data structure (I took the first one case) and the first status column to process it into wide format. Th out put will be one row for one school with additional 10 columns, each columns is a group's status for a subject, like ELA.

sch_id groupid group EM_ELA_StatusCY EM_Math_StatusCY HS_ELA_StatusCY HS_Math_StatusCY

1 1 1 GS:MP GS:MP - -

1 4 2 - - - -

1 5 3 - - - -

1 6 4 - - - -

1 7 5 - - - -

1 8 6 - - - -

1 9 7 GS:MP GS:MP - -

1 13 8 - - - -

1 14 9 GS:WAA GS:WAA - -

1 25 10 - - - -

Are all those dashes indicating missing data? I am not sure why you make things "wide" with missing data.

Maybe it is time to ask what this somewhat ugly data set is to be used for. And possibly where that particular structure originate?

With the column heading you show in that source this starts to sound like a REPORT may be easier to make then a very ugly data set if the data is not used for further analysis.

Re: Run SAS array when array value is not consecutive

Kurt_Bremser — Wed, 15 Jul 2020 07:33:49 GMT

@Petergao1 wrote:

I have program that run the transpose job. Now I am seeking alternative way to have it done differently.

PROC TRANSPOSE is the way to go for this. Don't waste brain cycles ín a futile attempt to reinvent the wheel. Use your time for more useful things.

Just compare this:

data have;
input sch_id $ subgroup EM_ELA_statusCY $;
datalines;
A 1 abc
A 13 def
A 14 ghi
A 22 xyz
;

proc transpose data=have out=want prefix=EM_ELA_statusCY;
by sch_id;
var EM_ELA_statusCY;
id subgroup;
run;

with your complicated array code, the need to convert the subgroups to a continuous series, and the re-translation from that. Which one of those codes will you want to maintain in the future?

Re: Run SAS array when array value is not consecutive

Petergao1 — Thu, 16 Jul 2020 12:52:23 GMT

The data set is real life file with just the column names modified for simplicity.

Yesterday or earlier I discovered I can run my job using the array dimension 1:25 and do i=1 to 25 without any modification to my data structure, it will run even though the real variable is not consecutive and will produce all those columns, when the subgroups have no value their output columns will have all missing values. All I need to do is in the keep, retain line, I list only those columns that have a value on subgroups and where they should have data (some missing is considered to have data).

Previous my program won't run because I forget to define the output columns to be character ones.

DATA want_wide;

SET have_long;

BY sch_id;

KEEP sch_id

EM_ELA_status1 EM_ELA_status4-EM_ELA_status9 EM_ELA_status13-EM_ELA_status14 EM_ELA_status25

EM_Math_status1 EM_Math_status4-EM_Math_status9 EM_Math_status13-EM_Math_status14 EM_Math_status25

HS_ELA_status1 HS_ELA_status4-HS_ELA_status9 HS_ELA_status13-HS_ELA_status14 HS_ELA_status25

HS_Math_status1 HS_Math_status4-HS_Math_status9 HS_Math_status13-HS_Math_status14 HS_Math_status25;

RETAIN sch_id

EM_ELA_status1 EM_ELA_status4-EM_ELA_status9 EM_ELA_status13-EM_ELA_status14 EM_ELA_status25

EM_Math_status1 EM_Math_status4-EM_Math_status9 EM_Math_status13-EM_Math_status14 EM_Math_status25

HS_ELA_status1 HS_ELA_status4-HS_ELA_status9 HS_ELA_status13-HS_ELA_status14 HS_ELA_status25

HS_Math_status1 HS_Math_status4-HS_Math_status9 HS_Math_status13-HS_Math_status14 HS_Math_status25;

ARRAY emela (1:25) $ EM_ELA_status1 - EM_ELA_status25 ;

ARRAY emmath (1:25) $ EM_Math_status1 - EM_Math_status25 ;

ARRAY hsela (1:25) $ HS_ELA_status1 - HS_ELA_status25 ;

ARRAY hsmath (1:25) $ HS_Math_status1 - HS_Math_status25 ;

IF first.sch_id THEN DO;

DO i = 1 to 25 ;

emela( i ) = "" ;

emmath (i) = "";

hsela( i ) = "" ;

hsmath (i) = "";

END;

emela (groupid) = EM_ELA_status;

emmath (groupid) = EM_Math_status;

hsela (groupid) = HS_ELA_status;

hsmath (groupid) = HS_Math_status;

IF last.sch_id THEN OUTPUT ;

RUN; *WORKS WELL;

*advantages: have less codes and programs than using transpose approach (code);

*shortcoming: output columns name have subgroup's numeric values instead of subgroup names-this is required by business convention, therefore I have to do further work to modify those 40 column names ending with number (1,4,5,6,7,8,9,13,14,25) into group names such as "All_students, SWD, Native_Amer, etc. after the main part of EM_ELA_status;

Re: Run SAS array when array value is not consecutive

Petergao1 — Thu, 16 Jul 2020 13:44:38 GMT

I wished I can do that. I am new to this group and like to see your previous example or answers. Thanks!

Re: Run SAS array when array value is not consecutive

Tom — Thu, 16 Jul 2020 15:48:51 GMT

Make your data taller (longer) first

proc transpose data=have_long out=long_long ;
  by sch_id groupid;
  var EM_ELA_status EM_Math_status HS_ELA_status HS_Math_status;
run;

and then run PROC TRANSPOSE.

proc transpose data=long_long out=want_wide(drop=_name_) ;
   by sch_id;
   var col1;
   id _name_ groupid;
run;

Re: Run SAS array when array value is not consecutive

Reeza — Thu, 16 Jul 2020 17:04:00 GMT

Learning how to search is worth the time.

Search terms used in the giant 'Find your SAS answers' above:

"delete variable missing"

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&q=delete%20variable%20missing

@Petergao1 wrote:

I wished I can do that. I am new to this group and like to see your previous example or answers. Thanks!