topic Re: sas sql : group by and missing values in SAS Programming

sas sql : group by and missing values

LineMoon — Sat, 11 Feb 2017 13:07:04 GMT

Hello,

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

Can you show some examples.

Re: sas sql : group by and missing values

mkeintz — Sat, 11 Feb 2017 14:13:47 GMT

@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?

Re: sas sql : group by and missing values

Reeza — Sat, 11 Feb 2017 21:15:26 GMT

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

Re: sas sql : group by and missing values

Saraja — Wed, 20 Feb 2019 20:54:27 GMT

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

Re: sas sql : group by and missing values

Reeza — Wed, 20 Feb 2019 21:01:43 GMT

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