BookmarkSubscribeRSS Feed
CG1
Calcite | Level 5 CG1
Calcite | Level 5

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

 

7 REPLIES 7
ballardw
Super User

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
Calcite | Level 5 CG1
Calcite | Level 5

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
Super User

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
Calcite | Level 5 CG1
Calcite | Level 5

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
Super User

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
Opal | Level 21

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.

PG
Ksharp
Super User

or proc freq.

 

proc freq .....

table prod_id*ord_dt/out=want list ;

format ord_dt MONYY5.;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1835 views
  • 2 likes
  • 4 in conversation