CODEWITHSUNDEEP
sas
learnlearn10
learnlearn10

Reputation: 169

Check if a column exists and then sum in SAS

This is my input dataset:

Ref  Col_A0  Col_01  Col_02  Col_aa  Col_03  Col_04  Col_bb
NYC   10       0       44     55       66      34      44
CHG   90       55       4     33       22      34      23
TAR   10       8        0     25       65      88      22

I need to calculate the % of Col_A0 for a specific reference. For example % col_A0 would be calculated as 

10/(10+0+44+55+66+34+44)=.0395 i.e. 3.95%

So my output should be

Ref %Col_A0 %Rest NYC 3.95% 96.05% CHG 34.48% 65.52% TAR 4.58% 95.42%

I can do this part but the issue is column variables. Col_A0 and Ref are fixed columns so they will be there in the input every time. But the other columns won't be there. And there can be some additional columns too like Col_10, col_11 till col_30 and col_cc till col_zz. For example the input data set in some scenarios can be just:

Ref  Col_A0  Col_01  Col_02  Col_aa  Col_03
NYC   10       0       44     55       66 
CHG   90       55       4     33       22 
TAR   10       8        0     25       65

So is there a way I can write a SAS code which checks to see if the column exists or not. Or if there is any other better way to do it.

This is my current SAS code written in Enterprise Guide.

PROC SQL;
CREATE TABLE output123 AS
select 
ref,
(col_A0/(Sum(Col_A0,Col_01,Col_02,Col_aa,Col_03,Col_04,Col_bb)) FORMAT=PERCENT8.2 AS PERCNT_ColA0,
(1-(col_A0/(Sum(Col_A0,Col_01,Col_02,Col_aa,Col_03,Col_04,Col_bb))) FORMAT=PERCENT8.2 AS PERCNT_Rest
From Input123;
quit;

Scenarios where all the columns are not there I get an error. And if there are additional columns then I miss those. Please advice.

Thanks

Upvotes: 0

Views: 1409

Answers (2)

Joe
Joe

Reputation: 63424

I would not use SQL, but would use regular datastep.

data want;
 set have;
 a0_prop = col_a0/sum(of _numeric_);
run;

If you wanted to do this in SQL, the easiest way is to keep (or transform) the dataset in vertical format, ie, each variable a separate row per ID. Then you don't need to know how many variables there are to figure it out.

Upvotes: 0

Longfish
Longfish

Reputation: 7602

If you always want to sum all the numeric columns then just do :

col_A0 / sum(of _numeric_)

Upvotes: 0

Related Questions