turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Percentage of time for a numerical variable to ass...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

4 weeks ago

Dear all,

I have a dataset as shown below. ID is the subject ID and each of the variables Var1 and Var2 are measured every minute and can both have missing values.

Dataset HAVE:

ID Minutes Var1 Var2

A 1 -3.2 -5.1

A 2 . -3.6

A 3 . -1.1

A 4 5.2 -4.9

B 1 3.2 1.2

B 2 4.5 1.2

B 3 3.1 4.2

C 1 . 1.9

C 2 . -2.2

C 3 2.1 -3.1

I would like to count the number of times each ID has a value of Var1 and Var2 below (e.g.) zero. This I would like to output as a new dataset showing for each ID, the period of time (one entry is one minute) each of the two variables had a value below zero, and the percentage/ratio of values below zero to the total number of entries (excluding missing values), e.g. like:

Dataset WANT:

ID Var1_minutes_below Var2_minutes_below Var1_perc_below Var2_perc_below

A 2 4 0.5 1

B 0 0 0 0

C 0 2 0 0.66

So far, I've been using SAS only for simple data management/statistics and this task I can't solve. Have tried reading a lot of posts solving similar problems using SQL/arrays, but I only have experience in the data step. I hope someone can help me with this?

Kind regards,

Alexander

Accepted Solutions

Solution

4 weeks ago

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AlexanderL

4 weeks ago

One way:

Please note the data step as the way to provide data that we can test with.

data have; input ID $ Minutes Var1 Var2; if not missing(var1) then var1_below= (. < var1 <0) ; if not missing(var2) then var2_below= (. < var2 <0) ; datalines; A 1 -3.2 -5.1 A 2 . -3.6 A 3 . -1.1 A 4 5.2 -4.9 B 1 3.2 1.2 B 2 4.5 1.2 B 3 3.1 4.2 C 1 . 1.9 C 2 . -2.2 C 3 2.1 -3.1 ; run; proc summary data=have nway; class id; var var1_below var2_below; output out=havesum (drop= _:) sum= n= /autoname; run; data want; set havesum; var1_perc_below = var1_below_sum/ var1_below_n; var2_perc_below = var2_below_sum/ var2_below_n; rename var1_below_sum=var1_below_minutes var2_below_sum=var2_below_minutes ; drop var1_below_n var2_below_n; run;

to calculate a percent you need to have a denominator. I am inferring that you want the number of records.

I am creating separate _N variables as you may have data at some point where you have values that you may not want to include for the denominator so getting in the habit of identifying each N is good idea.

Note if you want a report you could use a different approach with a report procedure

proc tabulate data= have; class id; var var1_below var2_below; table id='', (var1_below="Var1 minutes Below" var2_below="Var2 minutes Below")*sum=""*f=best5. (var1_below="Var1 % minutes Below" var2_below="Var2 % minutes Below")*mean=""*f=best5. /box=id ; run;

All Replies

Solution

4 weeks ago

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AlexanderL

4 weeks ago

One way:

Please note the data step as the way to provide data that we can test with.

data have; input ID $ Minutes Var1 Var2; if not missing(var1) then var1_below= (. < var1 <0) ; if not missing(var2) then var2_below= (. < var2 <0) ; datalines; A 1 -3.2 -5.1 A 2 . -3.6 A 3 . -1.1 A 4 5.2 -4.9 B 1 3.2 1.2 B 2 4.5 1.2 B 3 3.1 4.2 C 1 . 1.9 C 2 . -2.2 C 3 2.1 -3.1 ; run; proc summary data=have nway; class id; var var1_below var2_below; output out=havesum (drop= _:) sum= n= /autoname; run; data want; set havesum; var1_perc_below = var1_below_sum/ var1_below_n; var2_perc_below = var2_below_sum/ var2_below_n; rename var1_below_sum=var1_below_minutes var2_below_sum=var2_below_minutes ; drop var1_below_n var2_below_n; run;

to calculate a percent you need to have a denominator. I am inferring that you want the number of records.

I am creating separate _N variables as you may have data at some point where you have values that you may not want to include for the denominator so getting in the habit of identifying each N is good idea.

Note if you want a report you could use a different approach with a report procedure

proc tabulate data= have; class id; var var1_below var2_below; table id='', (var1_below="Var1 minutes Below" var2_below="Var2 minutes Below")*sum=""*f=best5. (var1_below="Var1 % minutes Below" var2_below="Var2 % minutes Below")*mean=""*f=best5. /box=id ; run;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AlexanderL

4 weeks ago

Hi , assuming the missing the value as zero (which is not less than zero but equal to zero) and some of the var1 and var2 are negative (which is less than zero)

please try like this

```
data have;
input ID$ Minutes Var1 Var2;
cards;
A 1 -3.2 -5.1
A 2 . -3.6
A 3 . -1.1
A 4 5.2 -4.9
B 1 3.2 1.2
B 2 4.5 1.2
B 3 3.1 4.2
C 1 . 1.9
C 2 . -2.2
C 3 2.1 -3.1
;
run;
data have1;
set have;
if var1 < 0 then Var1_minutes_below = var1;
if var2 <0 then Var2_minutes_below=var2;
run;
proc sql;
create table have2 as select id , max(Minutes) as minutes from have
group by id;
quit;
proc sql;
create table have3 as select id , count(Var1_minutes_below) as Var1_minutes_below,
count(Var2_minutes_below) as Var2_minutes_below from have1
group by 1;
quit;
proc sql;
create table want as
select a.*, a.Var1_minutes_below/b.minutes as Var1_perc_below ,
a.Var2_minutes_below/b.minutes as Var2_perc_below
from have3 as a left join have2 as b
on a.id = b.id;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to soham_sas

4 weeks ago

Thank you so much for your reply! This solution works absolutely fine! As I'm not used to working with proc sql, I think I will start with the solution posted above using only the data step, which I'm more familiar with.

Regards,

Alexander