incorrect if logic

[tmcrouse]

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;

[DBailey · Accepted solution]

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;

or

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;

[Linlin]

HI,

changing

if sub_table is not null then flag = 1;

to

if not missing( sub_table) then flag=1;

[Astounding]

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.