BookmarkSubscribeRSS Feed
LineMoon
Lapis Lazuli | Level 10

Hello,

Please, what is the impact of usinng sql group by and missing values.

Can you show some examples.

 

4 REPLIES 4
mkeintz
PROC Star

@LineMoon wrote:

Hello,

Please, what is the impact of usinng sql group by and missing values.

Can you show some examples.

 


How about you showing examples of "group by and missing values" that you find counterintuitive?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reeza
Super User

To solve a problem like this, consider all situations:

 

0. No Missing values

1. Missing values in data you're going to summarize

2. Missing values in a GROUP BY variables

3. Missing values in multiple GROUP BY variables

4. Missing values in data and GROUP BY variables.

 

Generate test data for each scenario and test it. PROC COMPARE can be used to compare the output. 

 

If you run into issues, post the sample test data and the code you used, and the log. 

 

Also, consider reviewing the post here on how to ask a good question.

http://stackoverflow.com/help/how-to-ask

 

 

 

Saraja
Fluorite | Level 6

Let me ask this question on the author's behalf so that we will have some meaningful answered down the line:

 

We have multiple decors by the ID. There are missing values due the fact that there are multiple records per ID instead on unique values. 

IDage2015age2016BP2015BP2016Weight2015Weight2016
155 235 178 
1 56 201 154

 

Using ID as a grouping variable how we can merge both records into single one so that there will be no more missing values such as here:

IDage2015age2016BP2015BP2016Weight2015Weight2016
15556235201178154

 

Also, there are several hundred such variables, can we run the code without naming the variables explicitly.

 

Thanks!

Sartaj

 

Reeza
Super User

Yes, this is trivial and answered in multiple places on here. Posting on a two-year-old thread is not advised.

 

data want;
update  have(obs=0) have;
by ID;
run;

@Saraja wrote:

Let me ask this question on the author's behalf so that we will have some meaningful answered down the line:

 

We have multiple decors by the ID. There are missing values due the fact that there are multiple records per ID instead on unique values. 

ID age2015 age2016 BP2015 BP2016 Weight2015 Weight2016
1 55   235   178  
1   56   201   154

 

Using ID as a grouping variable how we can merge both records into single one so that there will be no more missing values such as here:

ID age2015 age2016 BP2015 BP2016 Weight2015 Weight2016
1 55 56 235 201 178 154

 

Also, there are several hundred such variables, can we run the code without naming the variables explicitly.

 

Thanks!

Sartaj

 


 

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
  • 4 replies
  • 2121 views
  • 2 likes
  • 4 in conversation