topic Re: How to count number of observations in multi-condition in SAS Programming

How to count number of observations in multi-condition

lixuan — Wed, 02 Aug 2017 13:54:54 GMT

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
;;;;

Re: How to count number of observations in multi-condition

collinelliot — Wed, 02 Aug 2017 14:03:16 GMT

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.

Re: How to count number of observations in multi-condition

dancy0824 — Wed, 02 Aug 2017 14:10:28 GMT

Result:

Re: How to count number of observations in multi-condition

andreas_lds — Wed, 02 Aug 2017 14:32:06 GMT

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.

Re: How to count number of observations in multi-condition

lixuan — Wed, 02 Aug 2017 14:33:45 GMT

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

Re: How to count number of observations in multi-condition

ballardw — Wed, 02 Aug 2017 19:42:27 GMT

@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.