make a new variable based on two observations in a table

[mmea]

Hi

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

[Amir · Accepted solution]

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.

[mklangley]

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;

[mmea]

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.

[Amir]

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.