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;
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;
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;
HI,
changing
if sub_table is not null then flag = 1;
to
if not missing( sub_table) then flag=1;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.