How to count number of observations in multi-condition

[lixuan]

Hi, my data was simplified as following, and I wanna count the number of obsevations whose cusip is unique and yymm_a is unique. In this dataset, I wanna the result will be 4. How can I do this ?Thanks.

data WORK.TMP;
  infile datalines dsd truncover;
  input cusip:$8. yymm_a:YYMMN6.;
  format yymm_a YYMMN6.;
  label cusip="CUSIP/SEDOL";
datalines4;
00036020,199801
00036020,199801
00036020,199801
00036020,199801
00036020,199801
00036020,199801
00036020,199801
00036020,199801
00036020,199801
00036110,199705
00036110,199705
00036110,199705
00036110,199705
00036110,199705
00036110,199705
00036110,199705
00036110,199705
00036110,199705
00036110,199705
00036110,199810
00036110,199810
00036110,199810
00036110,199810
00036110,199810
00036110,199810
00036110,199810
00036110,199810
00036110,199810
00036110,199810
00036110,200009
;;;;

[collinelliot · Accepted solution]

You could do this:

proc sql;
    SELECT count(DISTINCT cusip||put(yymm_a, date9.)) FROM your_data;
quit;

You could also do a frequency table of the two, output a dataset, and get the count of observations.

[lixuan]

Hi, thanks a lot. I can't imagine || can be used like this .

[dancy0824]

Result:

[andreas_lds]

Interesting idea, i would replace the counting data-step with proc means or with a query selecting nobs from sashelp.vtable. Would be interesting to see which soiution is more efficient when large datasets are involved.

[ballardw]

@dancy0824 Please post code as text, best is in a code box using the forum {i} menu icon or the SAS run icon.

Pictures that require others to retype the code discourage actually testing/using the code.