DATA Step, Macro, Functions and more

How to count number of observations in multi-condition

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 97
Accepted Solution

How to count number of observations in multi-condition

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

Accepted Solutions
Solution
‎08-02-2017 10:57 AM
PROC Star
Posts: 307

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

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.

View solution in original post


All Replies
Solution
‎08-02-2017 10:57 AM
PROC Star
Posts: 307

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

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.

Frequent Contributor
Posts: 97

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

Posted in reply to collinelliot

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

New Contributor
Posts: 3

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

C1.PNG

 

Result:

 

C2.PNG

Super Contributor
Posts: 345

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

Posted in reply to dancy0824

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.

Super User
Posts: 11,343

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

Posted in reply to dancy0824

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 164 views
  • 4 likes
  • 5 in conversation