topic Re: Need to separate id from 3,000 plus record to do simple analysis per each i in SAS Programming

Continuous do-loop with counter

nickbuon01 — Fri, 15 Dec 2023 18:52:38 GMT

need some help. in the process of writing a short macro. I have a dataset of about 3,000 records. I need to separate each ID, then do some analysis then create a table. I need to go back and get the next id records and do the same and so on. I have the beginning but i do not know how to count the each record set. I have created a rownum for each set

of records. any ideas would be useful. Below is an example. and some Do-loop code. I know i will need to figure how to go down the record listof rownum's. Thank you in adavnce.

Dx_Level,Dx_Condition,Dx_level,Count,Countpermonth,allwd pay,allwd expected,Rownum

904,Ischemic heart diseases,51,1715,2366,159617812.6,67463.15,1

904,Ischemic heart diseases,31,18873,41249,177850913.8,4311.64,1

904,Ischemic heart diseases,33,3088,3597,76769181.5,21342.56,1

904,Ischemic heart diseases,52,672,737,63219340.64,85779.3,1

904,Ischemic heart diseases,32,9185,12286,113050036.7,9201.53,1

904,Ischemic heart diseases,42,4264,4949,305570930.4,61743.97,1

904,Ischemic heart diseases,53,1124,1147,301700925,263034.81,1

904,Ischemic heart diseases,43,3366,3629,459381125.8,126586.15,1

904,Ischemic heart diseases,41,8770,14542,421131055.4,28959.64,1

904,Ischemic heart diseases,12,247,260,533137.16,2050.53,1

904,Ischemic heart diseases,13,81,84,164342.09,1956.45,1

904,Ischemic heart diseases,23,247,265,2178525.94,8220.85,1

904,Ischemic heart diseases,21,1819,3739,4042923.97,1081.28,1

904,Ischemic heart diseases,22,333,371,1864851.64,5026.55,1

904,Ischemic heart diseases,0,18859,67472,0,0,1

904,Ischemic heart diseases,11,1396,2434,1367565.15,561.86,1

201,Malignant neoplasm of lip,13,1,1,81202.74,81202.74,2

201,Malignant neoplasm of lip,11,38,54,564851.15,10460.21,2

201,Malignant neoplasm of lip,12,16,18,611165.65,33953.65,2

201,Malignant neoplasm of lip,0,11,38,0,0,2

303,Malignant neoplasm of base of tongue,13,44,49,5139455.73,104886.85,3

303,Malignant neoplasm of base of tongue,0,164,414,0,0,3

303,Malignant neoplasm of base of tongue,12,232,338,8651302.77,25595.57,3

303,Malignant neoplasm of base of tongue,11,498,1059,25156595.02,23755.05,3

data want;

set work.HJICLter;

count = 1;

do while (rownum=count);

if rownum = rownum then count + 1;

output;

end;

run;

I have more to do. but the above with a counter to go thru separately each rownum and just create one sas table and understand how to move the rownum to the next record would be helpful.

Thank you.

Need to separate id from 3,000 plus record to do simple analysis per each i

nickbuon01 — Fri, 15 Dec 2023 15:52:55 GMT

Dx_Level	Dx_Condition	Dx_level	Count	Countpermonth	allwd pay	allwd expected	Rownum
904	Ischemic heart diseases	51	1715	2366	159617812.6	67463.15	1
904	Ischemic heart diseases	31	18873	41249	177850913.8	4311.64	1
904	Ischemic heart diseases	33	3088	3597	76769181.5	21342.56	1
904	Ischemic heart diseases	52	672	737	63219340.64	85779.3	1
904	Ischemic heart diseases	32	9185	12286	113050036.7	9201.53	1
904	Ischemic heart diseases	42	4264	4949	305570930.4	61743.97	1
904	Ischemic heart diseases	53	1124	1147	301700925	263034.81	1
904	Ischemic heart diseases	43	3366	3629	459381125.8	126586.15	1
904	Ischemic heart diseases	41	8770	14542	421131055.4	28959.64	1
904	Ischemic heart diseases	12	247	260	533137.16	2050.53	1
904	Ischemic heart diseases	13	81	84	164342.09	1956.45	1
904	Ischemic heart diseases	23	247	265	2178525.94	8220.85	1
904	Ischemic heart diseases	21	1819	3739	4042923.97	1081.28	1
904	Ischemic heart diseases	22	333	371	1864851.64	5026.55	1
904	Ischemic heart diseases	0	18859	67472	0	0	1
904	Ischemic heart diseases	11	1396	2434	1367565.15	561.86	1
201	Malignant neoplasm of lip	13	1	1	81202.74	81202.74	2
201	Malignant neoplasm of lip	11	38	54	564851.15	10460.21	2
201	Malignant neoplasm of lip	12	16	18	611165.65	33953.65	2
201	Malignant neoplasm of lip	0	11	38	0	0	2
303	Malignant neoplasm of base of tongue	13	44	49	5139455.73	104886.85	3
303	Malignant neoplasm of base of tongue	0	164	414	0	0	3
303	Malignant neoplasm of base of tongue	12	232	338	8651302.77	25595.57	3
303	Malignant neoplasm of base of tongue	11	498	1059	25156595.02	23755.05	3

Above is an example. I have so far been able to create a do-loop :

but it only separates one rownum example. I have a count for the rownum to be added next. But i am missing how to keep the added count to get to the next rownum .

data want;
set work.HJICLter;
count = 1;
do while (rownum=count);
if rownum = rownum then count + 1;
output;
end;
run;

I just need to go back thru and separate each rownum and do separate analysis and then have a final dataset of each rownum appended to a final dataset.

Any help would be appreciated.

Re: Continuous do-loop with counter

PaigeMiller — Fri, 15 Dec 2023 19:47:26 GMT

So, I don't see how a macro would help here. First, the looping in SAS to create rownum can be made much simpler than your code.

data want;
    set have;
    by dx_level notsorted;
    if first.dx_level then rownum+1;
run;

As far as performing some "analysis", it would really help if you told us what analysis you want to do. Many analyses can be done without a macro in the appropriate PROC using a BY statement. And so no looping is needed, and no macro is needed. In fact, in that case, you don't even need to compute ROWNUM. Example using PROC MEANS

proc means data=have;
    by dx_level notsorted;
    var count countpermonth allwd;
run;

By the way, you say "I need to separate each ID" but there is no variable named ID, so its not clear what you mean here.

Re: Continuous do-loop with counter

nickbuon01 — Fri, 15 Dec 2023 19:48:56 GMT

Paige,
Here is the code example:
data want;
set work.HJICLter;
count = 1;
do while (rownum=count);
if rownum = rownum then count + 1;
output;
end;
run;

proc fastclus data=work.test maxclusters=7 out=test_904 noprint;
var Dx_lvl;
id allwd_pay;
run;

proc append;
base=work.final;
data=work.test_904;
run;

Re: Continuous do-loop with counter

nickbuon01 — Fri, 15 Dec 2023 19:54:52 GMT

ID is Dx_Level. Also, the rownum separates all Dx_Level as one rownum

Re: Need to separate id from 3,000 plus record to do simple analysis per each i

HB — Fri, 15 Dec 2023 20:11:54 GMT

I have no idea what you want and because you have no other responses my guess is no one else does either.

If you want a count of records by rownum, that is a simple:

proc sql;
select
rownum, 
count(rownum)

from 
have

group by
rownum
;
quit;

But like I say, I have no idea what you want based on what you wrote.

You have two dx_level fields, allwd pay with a space isn't a valid filed name, etc.

Edit: Plus I see you have two threads with the same question(s)?

Re: Need to separate id from 3,000 plus record to do simple analysis per each i

nickbuon01 — Fri, 15 Dec 2023 20:14:45 GMT

This is what i want to do. each dx_level has a unique record, i created a rownum at the end. So i have 1,2,3
Below is the code i ma trying to get to for esch rownum to be used in the proc cluster data. example, rownum1, create table, use that table in the cluster take the output table from the cluster and start to create a final data using the proc append.
data wanta;
set work.want;
count = 1;
do while (rownum=count);
if rownum = rownum then count + 1;
output;
end;
run;

proc fastclus data=work.wanta maxclusters=7 out=test_904 noprint;
var Dx_lvl;
id allwd_pay;
run;

proc append;
base=work.final;
data=work.test_904;
run;

Re: Continuous do-loop with counter

PaigeMiller — Fri, 15 Dec 2023 20:21:17 GMT

proc fastclus data=work.test maxclusters=7 out=test_out noprint;
    by dx_level notsorted;
    id allwd_pay;
run;

No looping needed, no macros needed, no PROC APPEND needed. All the output will go into data set TEST_OUT, identified by dx_level number.

Re: Continuous do-loop with counter

PaigeMiller — Fri, 15 Dec 2023 20:22:33 GMT

PS: @nickbuon01 please don't submit the same question in two different threads.

Re: Need to separate id from 3,000 plus record to do simple analysis per each i

PaigeMiller — Fri, 15 Dec 2023 20:28:11 GMT

Please see my answer in your other thread

https://communities.sas.com/t5/SAS-Programming/Continuous-do-loop-with-counter/m-p/908371

Please do not submit the same question in multiple threads. One thread is sufficient.

Re: Continuous do-loop with counter

Kurt_Bremser — Fri, 15 Dec 2023 20:24:55 GMT

This

if rownum = rownum then count + 1;

makes no sense. The condition will always be true, so this is equivalent to

count + 1;

Re: Continuous do-loop with counter

Patrick — Sat, 16 Dec 2023 02:30:01 GMT

Just as a side note because the code you shared makes me think you haven't fully grasped yet how a data step works in SAS9.

data want;
  set work.HJICLter;
  count = 1;
  do while (rownum=count);
    if rownum = rownum then
      count + 1;
    output;
  end;
run;

The SET statement will already cause the data step to iterate over the source table (sequential single threaded read, one data step iteration/loop per row in the source table).

There is an automatic variable _n_ available during data step processing (not saved to the output table) that gets automatically incremented with +1 for each row read from source.

If you want an output table with a row number variable then simply assign _n_ to this variable. Here an example:

data want;
  set work.HJICLter;
  rownum=_n_;
run;

Re: Need to separate id from 3,000 plus record to do simple analysis per each i

Kurt_Bremser — Sat, 16 Dec 2023 06:48:05 GMT

I merged the threads.

Re: Need to separate id from 3,000 plus record to do simple analysis per each i

Tom — Sat, 16 Dec 2023 16:10:27 GMT

You don't have an ID number variable.

You appear to have DX_LEVEL and ROWNUM that both identify the same groups of observations.

If you want to do the same analysis for all groups then just add a BY statement to you procedure.

Since ROWNUM is sorted in ascending order you could use it:

proc means data=have;
  by rownum;
run;

But I suspect that DX_LEVEL is going to be more meaningful in the output. So you could perhaps just include both:

by rownum dx_level;

Or you could use just dx_level, but add the NOTSORTED keyword to let SAS know they are not in ascending order.

by dx_level notsorted;