Interestingly, once I have deleted the variable countbyyearspecialty and re-ran the command, I got the same results.    Thanks Everyone. ... View more

Thanks for your comment Ksharp. I get the following error message.    Data want; 43          set have; 44          by client year quarter; 45          array x{*} &list; NOTE: Line generated by the macro variable "LIST". 45          spec. spec0 spec1 spec10 spec11 spec13 spec16 spec17 spec18 spec2 spec20 spec21 spec22 spec23 spec24 spec26 spec27             _____             22             201 45       ! spec28 spec29 spec3 spec30 spec35 spec37 spec4 spec5 spec6 spec60 spec61 spec62 spec7 spec8 spec89 spec90 ERROR 22-322: Syntax error, expecting one of the following: a name, a numeric constant, $, (, ;, _ALL_, _CHARACTER_, _CHAR_,               _NUMERIC_, _TEMPORARY_.    ERROR 201-322: The option is not recognized and will be ignored.   46          retain &list; NOTE: Line generated by the macro variable "LIST". 46          spec. spec0 spec1 spec10 spec11 spec13 spec16 spec17 spec18 spec2 spec20 spec21 spec22 spec23 spec24 spec26 spec27             _____             22             201 46       ! spec28 spec29 spec3 spec30 spec35 spec37 spec4 spec5 spec6 spec60 spec61 spec62 spec7 spec8 spec89 spec90 ERROR 22-322: Syntax error, expecting one of the following: a name, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.    ERROR 201-322: The option is not recognized and will be ignored.   47          48         if first.quarter then do; 49          do i=1 to dim(x); 50           x{i}=0; 51          end; 52         end; 53          do i=1 to dim(x); 54           if cats('spec',specialty)=vname(x{i}) then do; 55             x{i}=count;leave; 56           end; 57          end; 58          ... View more

Astounding: I am trying to create a claims dataset with basic information on each claim, that I can merge in with the clients dataset. I have many (sometimes 400 claims) per client that I need information about in one dataset. Hence, the new dataset structure.      ... View more

KurtBremser: There are about 90 specialties. Thanks for your comment. I will try it.  ... View more

Thanks Yegen. In other words, this is not possible with Proc Tabulate...   One problem I have with this code, is that I actually have 90 specialties. Is there a way I don't have to manually generate 90 Spec variables?   Thanks again. ... View more

Hello Everyone,   I have a large dataset of health insurance claims of the following structure:   data claims; input client year quarter specialty; datalines; 1 2012 1 22 1 2012 1 22 1 2012 1 30 2 2013 2 10 2 2013 2 10 ; run;   I would like to summarize the number of claims per CLIENT by specialty and quarter of years. In other words, I would like to collapse my data by quarters.   My desired output would look something like this:   client year quarter Specialty Spec22 Spec10 spec30 1 2012 1 22 2 0 1 2 2013 2 10 0 2 0   Reeza on this forum suggested using proc tabulate (or something similar) and then proc transpose. But my problem is that I have 4.5 million claims from milions of clients. And this seems to be too much for SAS to write out.    This is what I've tried so far:   PROC tabulate DATA=work.claims out=claims_out; CLASS client year quarter; VAR specialty; tables client*year*quarter*specialty, N; title "Claims by Specialty and quarter"; run;   Any suggestions? ... View more

Hello Reeza,    I am trying your method with proc transpose. I need to see the number of claims per CLIENT by specialty and quarter of year. I have about 4 million claims from probably a few million different clients, multiplied by the number of periods.   I have this so far, but I somehow doubt that this is going to work for SAS:    PROC tabulate DATA=work.claims out=claims_out; CLASS client year quarter specialty; VAR specialty; tables client*year*quarter*specialty, N; title "Claims by Specialty and quarter"; run;   Any suggestions? ... View more

I didn't think of that. Thanks for the tip. ... View more

Hi ballardw,   Am I changing it to wide? Not exactly, because that wouldn't give me the sum of claims per quarter as the value. Correct?   It is difficult to explain exactly. Eventually, I would like to collapse the dataset by quarter of year. Similar to this:   client year quarter Specialty Spec22 Spec10 spec30 1 2012 1 22 2 0 1 2 2013 2 10 0 2 0   And merge this into my client dataset. At the moment, my problem is that i have several (sometimes 400) claims per client from different specialties and hospitals, and I can't merge the two dataset appropriately.    Thanks for your comment.  ... View more

Thanks for your help. I have other variables as well, but only those with the prefix client.    K.  ... View more

I tried both solutions and they both seem to work well. I accepted ballardw's solution just because he was first.    Thanks for your help.     ... View more