topic Re: make a new variable based on two observations in a table in New SAS User

make a new variable based on two observations in a table

mmea — Mon, 22 Feb 2021 14:46:20 GMT

I have this kind of table based on a proc compare data step:

	Total	Persons	Positive	date_or_text
1	1678899	4568788	294665	22feb2021
2	106113	5623	433	Changes since the day before

I need to add a new variable to this table with the positive percentages. it should take the "Positve" number from line 2 divided by the "total" number from line 2 times 100. it should be in mind that these numbers changes everyday. So I cant type the numbers manually in.

the wished ouput:

	Total	Persons	Positive	date_or_text	Pos_perc
1	1678899	4568788	294665	22feb2021	.
2	106113	5623	450	Changes since the day before	0,42

Re: make a new variable based on two observations in a table

Amir — Mon, 22 Feb 2021 15:43:08 GMT

Hi @mmea,

If you just want the percentage calculation done for observation number 2, then you could try something like:

data want;
   set have;

   if _n_ = 2 then
      pos_perc = (positive / total) * 100;
run;

If you want the calculation done for all observations then you can remove the line "if _n_ = 2 then".

If you want something different then please provide more information and explanation.

Thanks & kind regards,

Amir.

Re: make a new variable based on two observations in a table

mklangley — Mon, 22 Feb 2021 15:58:22 GMT

A few questions:

1. In your example, why isn't pos_perc calculated for the first row? Is there a rule for which rows to calculate?

2. Can you elaborate on how this data is updated? I presume it is a SAS dataset that is updated daily, and that you want to pos_perc calculation to be refreshed afterwards.

3. Is Positive in row 2 supposed to be 433 or 450? Your example shows both.

If you're looking for something that will automatically "refresh" the calculation, you could try a SAS view:

data have;
    input Total Persons Positive & date_or_text $32.;
    datalines;
1678899  4568788  294665  22feb2021
106113  5623  450  Changes since the day before
;
run;

proc sql;
    create view want as
    select *
          ,(positive / total) * 100 as pos_perc format 8.2
    from have;
quit;

Re: make a new variable based on two observations in a table

mmea — Tue, 23 Feb 2021 08:51:20 GMT

Thank you.

My mistake - the number should be the same for positives in both examples.

My data is updated everyday, so I need a refreshed number everyday.

Only for the second row - as the first row tells me the numbers for covid in the whole period - and the second row are today.

Re: make a new variable based on two observations in a table

Amir — Tue, 23 Feb 2021 09:01:58 GMT

Hi @mmea,

Thanks for the update. As no post has been marked as a solution, does this mean you still need a solution? In which case, please share what code you have tried, what result you received, and what result you actually wanted.

Thanks & kind regards,

Amir.