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

Hi  ,

 

Anyone pls help me ..I want to take the non missing value by ID variable .

 

Below is the sample code

 

Input :

 

Obs No  Low   High   ID

1    198        .         .     18
2     .            3        .     18
3     .            .         5    18

 

Desired Output: 

Obs  No Low High ID

1       198  3   5      18

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Like @RW9 I think the datastep update statement would be the easiest solution. Here is an example based on the data you provided:

 

data have ;
  input Obs No  Low   High   ID;
  cards;
1    198        .         .     18
2     .            3        .     18
3     .            .         5    18
;
 
data want ;
  update have (obs=0 keep=id) 
         have;
  by id;
run;

HTH,

Art, CEO, AnalystFinder.com

View solution in original post

6 REPLIES 6
ErikLund_Jensen
Rhodochrosite | Level 12

Hi

I think this will solve your problem:

 

proc sql;
     create table want as
          select distinct
              max(No) as No,
              max(Low) as Low,
              max(High) as High,
              ID
        from have
       group by ID;
quit;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

There are many topics here on this, am not typing that test data out so no code, but here is an example post:

https://communities.sas.com/t5/Base-SAS-Programming/Data-Step-Update-Statement/m-p/266809/highlight/...

 

You can use the update in a datastep.  If you want code post test data in the form of a datastep.

 

You could also just merge the three together.  However if you data is really just those three observations - without any kind of obs id variables (i.e. there is no ovariable which identifies those 3 records as being together), then how do you know to do that?   I.e. if you data looked like:

Obs No  Low   High   ID

1    198        .         .     18
2     .            3        .     18
3     .            .         5    18

4    199        .         .     18
5     .            5        .     18
6     .            .         5    18

 

How do you know that low=5 belongs to 199 and not 198?

ErikLund_Jensen
Rhodochrosite | Level 12

@RW9

 Given the data example in the original post, I expected the ID to be the observarion identifier, and in that case the problem you mention will not occur. 

 

But if there are several observations with 3 records for the same ID, the observation is held together only by the 3 records being in sequence in the data set, and we cannot sort data without losing that information. That rules out the SQL approach, but we can get the desired result with a data step, if we expect data to contain observations in 3 records with the Number-variable being the first .

 

data have;

No=198; Low=.; High=.; ID=18; output;

No=.; Low=3; High=.; ID=18; output;

No=.; Low=.; High=5; ID=18; output;

No=199; Low=.; High=.; ID=18; output;

No=.; Low=5; High=5; ID=18; output;

No=.; Low=.; High=.; ID=18; output;

No=158; Low=.; High=.; ID=11; output;

No=.; Low=3; High=.; ID=11; output;

No=.; Low=.; High=5; ID=11; output;

No=159; Low=.; High=.; ID=11; output;

No=.; Low=5; High=5; ID=11; output;

No=.; Low=.; High=.; ID=11; output;

run;

 

data want(drop=xNo xLow xHigh xID);

     set have(rename=(No=xNo Low=xLow High=xHigh ID=xID)) end=eof;

     retain No Low High ID;

     if xNo ne . and No ne . then output;

     if xNo ne . then do;

               No = xNo;

               ID = xID;

     end;

     if xLow ne . then Low = xLow;

     if xHigh ne . then High = xHigh;

     if eof then output;

run;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Do you know what, I didn't even see ID as a variable there!  Dataset update whould be able to do this fine.

DanielSantos
Barite | Level 11

Hi.

 

Seems you are looking for a group by transformation.

 

Here's another way of doing this by using the corresponding procedure:

 

 

proc summary data=HAVE nway missing;
class ID;
var NO LOW HIGH;
output out=WANT (drop = _:) max=;
run;

 

Hope it helps

 

Daniel Santos @ www.cgd.pt

art297
Opal | Level 21

Like @RW9 I think the datastep update statement would be the easiest solution. Here is an example based on the data you provided:

 

data have ;
  input Obs No  Low   High   ID;
  cards;
1    198        .         .     18
2     .            3        .     18
3     .            .         5    18
;
 
data want ;
  update have (obs=0 keep=id) 
         have;
  by id;
run;

HTH,

Art, CEO, AnalystFinder.com

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
  • 6 replies
  • 1015 views
  • 1 like
  • 5 in conversation