How can I get a count per record of a particular field in a file? 1

[GOUSC]

I am very very new to SAS and I have a file that I am reading where I will take the unique key say a claim number that can also have a line_no so you can have multiple line_no per claim_number. I have been told to put some of the fields in what we are calling a header file and a trailer file. Each file will also have a unique key that I am already building to be the key of the trailer and header files.

My problem is that I have some fields that we are not sure if they can have multiple or just one instance of the fields from the main file. If the particular field say spec_cd is on the main file per record multiple times, I need to put it in the trailer record, if it is only in the file one time then it will be on the header.

I was told to try using the Proc Mean procedure but it didn't seem to give me the information I was looking for so I was told to try the keep statement with a count but not sure how to go about this.

Another tidbit about the data, one of the fields is an amount. I have 3 records on the main file with different line numbers with 3 different amounts, that is pretty easy to assume that the amount would go on the line but for some of the other fields, I just would like to count per record for the fields Tennisanyone.

Again, any help I would apprecaite it.

 

[MatthiasB]

Hi TennisAnyone,

maybe like this - very early here now, not so clear in my head yet ;-)

data dummy;
   set theData (keep=key1 key2 key3 theKey);
   by key1 key2 key3 theKey;
   retain key_Cnt;
   if first.theKey then key_Cnt=0;
   key_Cnt = key_Cnt+1;
   if last.theKey then output;
run;

the table you look into 'TheData' has your key variables key1-3 and 'theKey' you want to check if it is unique per key1-3 combination. The table must be sorted by "key1 key2 key3 theKey", or you get an error message.

A counter is retained. Since we used the 'by' statement, we can you first/last, so on the 'first' we init the counter, for all records increment it, on 'last' per key sequence output. The result table will contain all unique key combinations and the frequency count. You can then just "fsv dummy (where=(key_cnt gt 1))".

The 'amount' thing I do not understand - can you give a bit more info?

Everyone a splendid working day! ;-)

Cheers,
Matthias

 

[GOUSC]

Thanks Matthias.

Your info helped greatly.

Later,
Tennisanyone