topic incorrect if logic in SAS Procedures

incorrect if logic

tmcrouse — Fri, 13 Sep 2013 13:30:42 GMT

I have 30k rows that have somewhat similiar values. The sub_table can have a possible 28 various values.

Data Have

mbr date tin sub_table

1 2012 22 opmajcath

2 2012 22 ofcmintte

3 2012 22 ip30pci

4 2012 22 op30pci

Data Need

mbr date tin sub_table flag

1 2012 22 opmajcath 1

2 2012 22 ofcmintte 1

3 2012 22 ip30pci 1

4 2012 22 op30pci 1

I have tried multiple if statements because I just need to place a number on each line that equals a count of 1 so that I can later do a summation of each of the sub_table items by the tin. Right now I cannot sum because the data have is char. I can count but if the sub_table has a like value for the tin, then it only counts 1 time. I already tried count and group by the tin, mbr and date, however................some mbrs might show up more than 1 time on the same date.

I tried this as my final but it did not do anything. Error on the is not null statement

data need;

data have;

if sub_table is not null then flag = 1;

run;

Re: incorrect if logic

DBailey — Fri, 13 Sep 2013 13:44:15 GMT

well...your data step should say:

data need;

set have;

if sub_table ne '' then flag = 1;

run;

However...If you're really looking for distinct values, then you could use the distinct option:

proc sql;

create table want as select distinct mbr, date, tin, sub_table, 1 as flag

from have

where sub_table is not null;

quit;

create table want as select mbr, date, tin, sub_table, 1 as flag from have where sub_table is not null group by mbr, date, tin, sub_table;

Re: incorrect if logic

Linlin — Fri, 13 Sep 2013 13:49:30 GMT

HI,

changing

if sub_table is not null then flag = 1;

if not missing( sub_table) then flag=1;

Re: incorrect if logic

Astounding — Fri, 13 Sep 2013 13:51:16 GMT

SAS is able to count. You don't have to create a new variable and add it up.

This might be an early step:

proc freq data=have;

tables mbr * date * tin * sub_table / noprint out=counts;

* possibly also adding: where sub_table > ' ';

run;

That will give you a data set COUNTS with one observation per mbr / date / tin / sub_table. Print a few observations, so you know what you are dealing with.

From that point, it's easy to get final counts:

proc freq data=counts;

tables tin;

tables sub_table*tin / list;

run;

There's lots of ways to get whatever you need as the final report.

Good luck.