topic Re: Pulling certain observations based on a cut off in a large dataset in New SAS User

Pulling certain observations based on a cut off in a large dataset

tadedir1 — Sun, 15 Sep 2019 21:29:50 GMT

Hi,

I currently have dataset with 388 observations(rows) and over 8,000 variables of interest(i.e. columns). The data is in a matrix form. The row contains study numbers and each column is a gene. Each cell contains a ratio from 0 to 1. I want to tell SAS for each column pull study numbers that greater than 0.8. I've been manually doing this and I am not sure if there is a more efficient way of doing this. I am not familiar with sas macros or arrays. Below is a table of the data structure.

Study Number	A	B	C	D	E	F
1	1	0.98	0.99	0.98	.8	0.98
2	.6	1	1	.4	1	1
3	1	.3	1	1	1	1

Re: Pulling certain observations based on a cut off in a large dataset

Jagadishkatam — Sun, 15 Sep 2019 21:43:56 GMT

Please try this untested code

data want;
set have;
array vars(*) A B C D F ;
do i = 1 to dim(vars);
if vars(i) ne . and vars(i)>0.8 output;
end;
run;

Re: Pulling certain observations based on a cut off in a large dataset

tadedir1 — Sun, 15 Sep 2019 21:52:46 GMT

If you had 8,000 columns, do you have to write out each variable name (i.e. vars (*) A B C D F...8000) or is there a faster way to doing it this way?

Re: Pulling certain observations based on a cut off in a large dataset

Jagadishkatam — Sun, 15 Sep 2019 22:20:45 GMT

We can use the proc sql to pull the variables into a macro variable as below ad then use this macro variable in place of the variables in array

proc sql;
select name into :vars separated by ' ' from dictionary.columns where libname='WORK' and memname='HAVE';
quit;

Re: Pulling certain observations based on a cut off in a large dataset

novinosrin — Sun, 15 Sep 2019 23:09:24 GMT

Hi @tadedir1

If your variables of interest are listed i.e occurs in sequence from left to right in succession, you could conveniently use variable lists with a double(--) .

For eg.

data want;
set have;
array vars(*) A --F ;
do i = 1 to dim(vars);
if  vars(i)>0.8 do;
 output;
leave;
end;
end;
run;

Exit the loop soon as a value>0.8 is found

Re: Pulling certain observations based on a cut off in a large dataset

tadedir1 — Sun, 15 Sep 2019 23:16:43 GMT

When I run the arrays displayed above: I'm still pulling variables that are less than 0.8.

Re: Pulling certain observations based on a cut off in a large dataset

novinosrin — Sun, 15 Sep 2019 23:19:47 GMT

Oh you mean you wanna drop variables?or observations? You should prolly show your expected output

Re: Pulling certain observations based on a cut off in a large dataset

tadedir1 — Sun, 15 Sep 2019 23:19:27 GMT

Yes I want to drop observations that don't meet the 0.8 criteria.

Re: Pulling certain observations based on a cut off in a large dataset

novinosrin — Sun, 15 Sep 2019 23:20:33 GMT

You should prolly show your expected output

Re: Pulling certain observations based on a cut off in a large dataset

tadedir1 — Sun, 15 Sep 2019 23:40:42 GMT

Expected output

Option: Set those with cut off less than 0.8 as missing

Study Number	A	B	C	D	E	F
1	1	0.98	0.99	0.98	.8	0.98
2	.	1	1	.	1	1
3	1	.	1	1	1	1

Have 6 different tables only displaying the cutoffs greater than or equal to 0.8

Study Number	A	B	C	D	E	F
1	1	0.98	0.99	0.98	.8	0.98
2	.6	1	1	.4	1	1
3	1	.3	1	1	1	1

Study Number a

1 1

3 1

study number b

1 0.98

2 1

study number c

1 0.99

2 1

3 1

Re: Pulling certain observations based on a cut off in a large dataset

novinosrin — Sun, 15 Sep 2019 23:52:58 GMT

Option: Set those with cut off less than 0.8 as missing - Easy

data want;
set have;
array vars(*) A --F ;
do i = 1 to dim(vars);
if  vars(i)<0.8 then var(i)=.;
end;
drop i;
run;

@tadedir1 wrote:

Expected output

Option: Set those with cut off less than 0.8 as missing

Study Number A B C D E F

1 1 0.98 0.99 0.98 .8 0.98

2 . 1 1 . 1 1

3 1 . 1 1 1 1