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
;;;;
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.
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.
Hi, thanks a lot. I can't imagine || can be used like this .
Result:
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.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.