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

Hello. I need to output two datasets. Here is what I need to do for each:

 

For dataset 1, if a mother has gestational diabetes in ANY pregnancynum_rank (in pregnancy 1,2...n), then I want to output a dataset of ALL her pregnancy visits (whether or not that pregnancy had gestational diabetes).

 

Dataset 1 -- data have:

visit_id       study_id      delivery_visit   pregnancynum_rank   gestational_diabetes

1                      1                     1                        1                                 0

2                      1                     0                        2                                 1

3                      1                     1                        2                                 1

4                      2                     0                        1                                 0

5                      2                     1                        1                                 0

6                      3                     0                        1                                 0

7                      3                     1                        1                                 1

8                      3                     1                        2                                 1

9                      4                     0                        1                                 0

10                    4                     1                        1                                 0

11                    4                     0                        2                                 1

12                    4                     1                        2                                 1

 

 

data want:

visit_id       study_id      delivery_visit   pregnancynum_rank   gestational_diabetes

1                      1                     1                        1                                 0

2                      1                     0                        2                                 1

3                      1                     1                        2                                 1

4                      2                     0                        1                                 0

5                      2                     1                        1                                 0

6                      3                     0                        1                                 0

7                      3                     1                        1                                 1

8                      3                     1                        2                                 1

9                      4                     0                        1                                 0

10                    4                     1                        1                                 0

11                    4                     0                        2                                 1

12                    4                     1                        2                                 1

 

For the next step, dataset 2, I want to choose the first pregnancy where the mom had gestational diabetes and output all her visits for that pregnancy only.

 

Dataset 2 -- data want:

visit_id       study_id      delivery_visit   pregnancynum_rank   gestational_diabetes

1                      1                     1                        1                                 0

2                      1                     0                        2                                 1

3                      1                     1                        2                                 1

4                      2                     0                        1                                 0

5                      2                     1                        1                                 0

6                      3                     0                        1                                 0

7                      3                     1                        1                                 1

8                      3                     1                        2                                 1

9                      4                     0                        1                                 0

10                    4                     1                        1                                 0

11                    4                     0                        2                                 1

12                    4                     1                        2                                 1

 

Much appreciated if you provide the code.  Thanks for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

second one is something you should have tried lol, but anyway:

 

data have;

input visit_id       study_id      delivery_visit   pregnancynum_rank   gestational_diabetes;

datalines;

1                      1                     1                        1                                 0

2                      1                     0                        2                                 1

3                      1                     1                        2                                 1

4                      2                     0                        1                                 0

5                      2                     1                        1                                 0

6                      3                     0                        1                                 0

7                      3                     1                        1                                 1

8                      3                     1                        2                                 1

9                      4                     0                        1                                 0

10                    4                     1                        1                                 0

11                    4                     0                        2                                 1

12                    4                     1                        2                                 1

;

 

proc sql;

create table want1 as

select *

from have

group by study_id

having max(gestational_diabetes)=1

order by visit_id;

quit;

 

proc sql;

create table want2 as

select *

from have

where gestational_diabetes=1;

quit;

 

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20

second one is something you should have tried lol, but anyway:

 

data have;

input visit_id       study_id      delivery_visit   pregnancynum_rank   gestational_diabetes;

datalines;

1                      1                     1                        1                                 0

2                      1                     0                        2                                 1

3                      1                     1                        2                                 1

4                      2                     0                        1                                 0

5                      2                     1                        1                                 0

6                      3                     0                        1                                 0

7                      3                     1                        1                                 1

8                      3                     1                        2                                 1

9                      4                     0                        1                                 0

10                    4                     1                        1                                 0

11                    4                     0                        2                                 1

12                    4                     1                        2                                 1

;

 

proc sql;

create table want1 as

select *

from have

group by study_id

having max(gestational_diabetes)=1

order by visit_id;

quit;

 

proc sql;

create table want2 as

select *

from have

where gestational_diabetes=1;

quit;

 

eabc0351
Quartz | Level 8

Lol, you're so right, but thanks for the reply. Can I implore you to take a look at the following thread (I'm stuck on the previous step to solve the problem you just answered):

 

1.). https://communities.sas.com/t5/Base-SAS-Programming/Don-t-know-term-or-code/m-p/396658#M95800 (directed as previous person who replied, ignore that)

 

 

Thanks!

 

novinosrin
Tourmaline | Level 20

Sure, I had a look. How may I help you?

eabc0351
Quartz | Level 8

Thank you. 

 

For any mothers that have multiple_gestation=1 for any of the visits within that pregancy number rank, then multiple_gestation=1 for all visits within the pregnancy number rank.

 

For example, see data below. Mother study_id=1 did not have gest_diab during her first pregnancy, but had gest_diabetes during her second preg. Need to get the gest_diabetes =1 for visit_id 2 & 3 for that mom (but not for 1st preg (visit_id 1)). Same with mom study_id 4. Her 1st pregnancy she did not have gest_diabetes, but had gest_diabetes during her second preg. Visit_id 11 & 12 for mom study_id 4 needs to equal 1, but her visit_id 9 and 10 should stay 0. 

The mothers with multiple_gestation=0 for all pregnancies would be 0 for all visits within all pregnancy ranks.

 

Data have:

visit_id       study_id      delivery_visit   pregnancynum_rank   gestational_diabetes

1                      1                     1                        1                                 0

2                      1                     0                        2                                 1

3                      1                     1                        2                                 0

4                      2                     0                        1                                 0

5                      2                     1                        1                                 0

6                      3                     0                        1                                 0

7                      3                     1                        1                                 1

8                      3                     1                        2                                 1

9                      4                     0                        1                                 0

10                    4                     1                        1                                 0

11                    4                     0                        2                                 1

12                    4                     1                        2                                 0

 

 

 

Data want:

visit_id       study_id      delivery_visit   pregnancynum_rank   gestational_diabetes

1                      1                     1                        1                                 0

2                      1                     0                        2                                 1

3                      1                     1                        2                                 1

4                      2                     0                        1                                 0

5                      2                     1                        1                                 0

6                      3                     0                        1                                 0

7                      3                     1                        1                                 1

8                      3                     1                        2                                 1

9                      4                     0                        1                                 0

10                    4                     1                        1                                 0

11                    4                     0                        2                                 1

12                    4                     1                        2                                 1

 

Hope that makes sense. Thanks  for your help!

novinosrin
Tourmaline | Level 20

Data have;
input visit_id study_id delivery_visit pregnancynum_rank gestational_diabetes;
datalines;
1 1 1 1 0
2 1 0 2 1
3 1 1 2 0
4 2 0 1 0
5 2 1 1 0
6 3 0 1 0
7 3 1 1 1
8 3 1 2 1
9 4 0 1 0
10 4 1 1 0
11 4 0 2 1
12 4 1 2 0
;

proc sql;
create table want(drop=gestational_diabetes) as
select *,max(gestational_diabetes) as _gestational_diabetes
from have
group by study_id,pregnancynum_rank;
quit;

eabc0351
Quartz | Level 8

Okay, I understand the PROC SQL statment. But what is the first part (below)?
Can I apply the PROC SQL to the data I have? Or do I need to use the following code somehow? 

 

Data have;
input visit_id study_id delivery_visit pregnancynum_rank gestational_diabetes;
datalines;
1 1 1 1 0
2 1 0 2 1
3 1 1 2 0
4 2 0 1 0
5 2 1 1 0
6 3 0 1 0
7 3 1 1 1
8 3 1 2 1
9 4 0 1 0
10 4 1 1 0
11 4 0 2 1
12 4 1 2 0
;

novinosrin
Tourmaline | Level 20

LoL the data have is merely the sample that you gave me as a input and I trust your sample is representative of the data you have. If it is the code should comfortably work. I really hope every OP(Original poster)'s sample is a good representative of their real data so that we the community(our) solutions work for them. 

eabc0351
Quartz | Level 8
So glad I could make you laugh today;) Thanks for all your help!
novinosrin
Tourmaline | Level 20

@eabc0351 You're welcome. My apologies for the laugh, but I didn't mean to. Have a good one!

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
  • 9 replies
  • 776 views
  • 1 like
  • 2 in conversation