BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Krysia24
Obsidian | Level 7

Feeling really frustrated this morning. Trying to do a very basic proc sql query and my output is not what I want it to be. Instead of having one row for each group, I am getting multiple rows with the same group. In my experience, this issue is a result of remerging - but I am not getting any errors in the log. 

 

Code: 

proc sql;
create table disease2 as
select
date_new,
count(*) as diseased
from disease1
group by date_new
order by date_new;

QUIT;

 

My output: 

date_new diseased 

202012 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 2
202101 2
202102 2
202102 2
202102 1
202102 2
202102 2
202102 5
202102 4
202102 3
202102 6
202102 2

 

Desired output: 

date_new diseased 

202012 1

202101 12 

202102  29

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

date_new is likely formatted.
SAS SQL doesn't honour formats on your data.

proc sql;
create table disease2 as
select
put(date_new, yymmn6.) as date_new,
count(*) as diseased
from disease1
group by put(date_new, yymmn6.)
order by calculated date_new;

QUIT;

or use PROC FREQ which does use formats.

 

proc freq data=disease1 noprint;
table date_new / out=disease_counts_by_date nocum nopercent;
run;

@Krysia24 wrote:

Feeling really frustrated this morning. Trying to do a very basic proc sql query and my output is not what I want it to be. Instead of having one row for each group, I am getting multiple rows with the same group. In my experience, this issue is a result of remerging - but I am not getting any errors in the log. 

 

Code: 

proc sql;
create table disease2 as
select
date_new,
count(*) as diseased
from disease1
group by date_new
order by date_new;

QUIT;

 

My output: 

date_new diseased 

202012 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 2
202101 2
202102 2
202102 2
202102 1
202102 2
202102 2
202102 5
202102 4
202102 3
202102 6
202102 2

 

Desired output: 

date_new diseased 

202012 1

202101 12 

202102  29

 

 

 


 

View solution in original post

3 REPLIES 3
Reeza
Super User

date_new is likely formatted.
SAS SQL doesn't honour formats on your data.

proc sql;
create table disease2 as
select
put(date_new, yymmn6.) as date_new,
count(*) as diseased
from disease1
group by put(date_new, yymmn6.)
order by calculated date_new;

QUIT;

or use PROC FREQ which does use formats.

 

proc freq data=disease1 noprint;
table date_new / out=disease_counts_by_date nocum nopercent;
run;

@Krysia24 wrote:

Feeling really frustrated this morning. Trying to do a very basic proc sql query and my output is not what I want it to be. Instead of having one row for each group, I am getting multiple rows with the same group. In my experience, this issue is a result of remerging - but I am not getting any errors in the log. 

 

Code: 

proc sql;
create table disease2 as
select
date_new,
count(*) as diseased
from disease1
group by date_new
order by date_new;

QUIT;

 

My output: 

date_new diseased 

202012 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 1
202101 2
202101 2
202102 2
202102 2
202102 1
202102 2
202102 2
202102 5
202102 4
202102 3
202102 6
202102 2

 

Desired output: 

date_new diseased 

202012 1

202101 12 

202102  29

 

 

 


 

Krysia24
Obsidian | Level 7

Thank you! I did not know formats were not honored in proc sql. Without an error or warning in the log, I was unsure what I did wrong. Thank you for your help. 

PGStats
Opal | Level 21

I suspect @Reeza is right, date_new values are dates and you want to summarize by month. You must transform the daily dates into month dates (e.g. the date of the first of the month) to calculate the counts.

 

proc sql;
create table disease2 as
select
	intnx("Month", date_new, 0) format=yymmn6. as month_new,
	count(*) as diseased
from disease1
group by calculated month_new;
quit;

Note INTNX("Month", date_new, 0) returns the date of the first day of the month.

PG

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3 replies
  • 3209 views
  • 6 likes
  • 3 in conversation