Duplicates by group

[CG1]

Hi team,

I am running below query

PROC SQL ;
select distinct prod_id,count(ord_num),ord_dt format=MONYY5. as dt
from hcl_mdm.fact_pub_subs_mdm
group by prod_id,ord_dt;
quit;

in this I need to extract prod id waise and MON-YYYY wise data. but above queyr is giving wrong output.

Could you please help me to resolve this

[ballardw]

How is it giving "wrong output"? No output, error message, unexpected value? If the values are not as expected then you need to provide information about the input data and the result and which variable(s) or records are getting the unexpected result.

I am going guess that it has something to do with the variable ORD_DT and not showing in the correct date format. My guess from the variable name ending of DT is that the varaible is a DateTime variable. If so then the the value is recorded in seconds and a date format such as MONYY expects the value to be days and the result either does not display or is showing a date much larger than expected.

If that is the case then you want to use: DATEPART(ord_dt) as dt format=Monnyy5.

[CG1]

Hi Below is the sample data

PROD_ID	Ord_dt	ord_num
010000000Z	Apr-16	2
010000000Z	Apr-16	1
010000000Z	Apr-87	21
010000000Z	Apr-87	22
010000000Z	Apr-88	29
020000000Z	Apr-88	23
020000000Z	Apr-90	33
020000000Z	Apr-90	61
020000000Z	Apr-16	10
020000000Z	Apr-16	20

output should be 

Prod_id	MONYY	Count
010000000Z	Apr-16	3
010000000Z	Apr-87	43
010000000Z	Apr-88	22
020000000Z	Apr-88	23
020000000Z	Apr-90	94
020000000Z	Apr-16	30

PROC SQL ;
select distinct prod_id,count(ord_num),ord_dt format=MONYY5. as dt
from hcl_mdm.fact_pub_subs_mdm
group by prod_id,ord_dt format=MONYY5.;
quit;

can you please correct the above query.

Thanks.

[ballardw]

Looks like you want

sum(ord_num),

instead of

count(ord_num),

count does exactly what its name implies: it returns the number of times the variable was not missing for the combination of the group by variables.

[CG1]

Hi,

I am getting issue while grouping the record

group by prod_id,ord_dt format=MONYY5.;

I am not getting issue with sum or count.

Please guide me how to convert date into MONYY character in group by statement

[ballardw]

Is your current variabe text or numeric?

If it is currently a SAS date valued numeric and you want an actual text variable then you have to explicitly convert it with something like:

put(datevariablename, MONYY5.) as Textdatevariablename

[PGStats]

I guess you either want

PROC SQL ;
select 
	prod_id,
	count(ord_num) as count_ord_num,
	intnx("MONTH", ord_dt, 0) format=MONYY5. as dt 
from hcl_mdm.fact_pub_subs_mdm
group by prod_id, calculated dt;
quit;

Or

PROC SQL ;
select 
	prod_id,
	count(ord_num) as count_ord_num,
	intnx("MONTH", datepart(ord_dt), 0) format=MONYY5. as dt 
from hcl_mdm.fact_pub_subs_mdm
group by prod_id, calculated dt;
quit;

depending if ord_dt is a SAS date or a SAS datetime.

[Ksharp]

or proc freq.

proc freq .....

table prod_id*ord_dt/out=want list ;

format ord_dt MONYY5.;

run;