topic Re: Array question in SAS Procedures

Array question

s100 — Fri, 17 May 2013 22:19:38 GMT

Hello,

ID	a1	a2	a3	a4	a5	x	y
1	1	1	1	0	1	1	3
2	1	0	1	0	1	2	4
3	0	1	0	1	0	3	5
4	1	1	1	1	1	1	5

My dataset looks like this. I created an array for a1-a5. The purpose is to select the records that all the a(i) values are equal to 1 ( i = x to y).

I am a new SAS user. Any suggestions would be appreciated.

Thanks in advance!

Re: Array question

Tom — Fri, 17 May 2013 23:04:50 GMT

If you only want to look at the values whose index is between x and y then you will probably need to code a loop.

Since you only want to keep those will all one's you can just DELETE when you see something that is not 1. This will end that iteration of the data step without outputting the record and immediately go to the next observation in the source.

data want ;

set have ;

array a a1-a5;

do i=x to y; if a(i) ne 1 then delete; end;

run;

Re: Array question

s100 — Fri, 17 May 2013 23:27:18 GMT

Thanks, Tom! That's helpful.

But in my real dataset, some people may have multiple records/observations. The requirement ( a(i)=1 ) may just apply to one of their records. If I use 'delete' function, that will only delete one observation per person. So I think output all the eligible people might be more easier. Any idea how to output those ids? Thanks!

Re: Array question

Reeza — Fri, 17 May 2013 23:31:02 GMT

What if the ID has situations where all a(i) are one in one observation and not in the other?

data want_id;

set have;

array a a1-a5;

if max(of a(*))=min(of a(*)) and max(of a(*))=1 then output;

keep id;

run;

proc sql;

create table want as

select * from have

where id in (select distinct id from want_id);

quit;

Re: Array question

Tom — Sat, 18 May 2013 00:07:33 GMT

So you want to keep all observations for any ID that has at least one record where all values from a(X) to a(Y) are one?

Try using a two DOW loops. The first will process all of the observations for that level of ID and set the flag ANY1 to true if any of them have all values of one.

The second DOW loop will process the same block of records and again and output them based on the ANY1 flag.

data want ;

any1 = 0;

do until (last.id);

set have ;

by id;

array a a1-a5;

anynot1 =0;

do i=x to y;

if a(i) ne 1 then anynot1=1;

end;

if not anynot1 then any1=1;

end;

do until (last.id);

set have ;

by id;

if any1 then output;

end;

run;

Re: Array question

felipeespi — Sat, 18 May 2013 00:38:27 GMT

Hi Tom I was wondering if you could help me with a simple but yet complex task that's driving me nuts.

My data set in excel

dog	cat	mice	camel	pigeon	cow	goat	fish	horse	donkey	monkey	lion	rat
9	7	0.25	2534	0.57	2243	18	4	3456	3312	23	1544	0.69

I want to create two variables 'animals' and 'weight' to match each animal in columns with respective weight without modifying the data. Just by using proc import and or proc sort.

proc import datafile='E:\Copy of final examination.xls' out=Animals dbms=xls replace;sheet=animals;run;

Re: Array question

Reeza — Sat, 18 May 2013 01:06:08 GMT

You should post your qeustion in a new thread.

And what do you mean by just using import and sort? You can't use tranpose or a data step?

I'm not sure its possible with just an import/transpose.

Re: Array question

felipeespi — Sat, 18 May 2013 02:21:13 GMT

Thanks Reeza.

We're not to use transpose for this specific question - I could try data step but I'm unsure of how to go about it.

Re: Array question

Tom — Sat, 18 May 2013 02:48:19 GMT

If your want to figure it out yourself look for a SAS function that you could with an array in a data step.

Re: Array question

Reeza — Sat, 18 May 2013 04:46:54 GMT

Try UCLA SAS website.

SAS Learning Module: Reshaping wide to long using a data step