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

Hi.

 

I am trying to scan data columns for sites for each ID.  I am looking for the following record (where there is no blank) and for that data value to be placed into a new column (Most Recent).  I am having difficulty trying to get SAS to bypass the record rows where there is a blank and to pick the next row containing data.  Can anyone shed some light? 

 

The first table shows the data the second table shows how I would like the results  .

 

Sanna_K_0-1682622152447.png

 

 

 

DATA Once;

 INPUT ID $ visit1 site1  ;

CARDS;

Inn342      null  null

Inn342      Anc         Sha

Inn342      Drr         Loc

Pan322                       

Pan322      Cli         Dha

Pan322      Omn         Sha

;

RUN;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Just use a period as a placeholder in the data line for missing value (numeric or character).

data once;
 input ID $ visit1 $ site1 $  ;
CARDS;
Inn342  .    .
Inn342  Anc  Sha
Inn342  Drr  Loc
Pan322  .    .
Pan322  Cli  Dha
Pan322  Omn  Sha
;

To move the data backwards in time you need make two passes thru the data.

 

You could do it with two separate SET statements.

data want;
  do until(last.id);
    set once;
    by id;
    length first_site $8;
    first_site=coalescec(first_site,site1);
  end;
  do until(last.id);
    set once;
    by id;
    output;
  end;
run;

Or you could merge it with itself.

data want;
  merge once 
        once(keep=id site1 rename=(site1=xxx) where=(not missing(xxx)))
  ;
  by id;
  if first.id then first_site=xxx;
  retain first_site;
  drop xxx;
run;

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

Your data step does not work because you try to read character data into numeric variables. Please fix that, either by making the variables character or providing the necessary code to convert the strings to numbers.

Once we know how your dataset really looks, we can help you.

PaigeMiller
Diamond | Level 26

@SannaSanna wrote:

Hi.

 

I am trying to scan data columns for sites for each ID.  I am looking for the following record (where there is no blank) and for that data value to be placed into a new column (Most Recent).  I am having difficulty trying to get SAS to bypass the record rows where there is a blank and to pick the next row containing data.  Can anyone shed some light? 

 

The first table shows the data the second table shows how I would like the results  .

 

Sanna_K_0-1682622152447.png

 

 

 

DATA Once;

 INPUT ID $ visit1 site1  ;

CARDS;

Inn342      null  null

Inn342      Anc         Sha

Inn342      Drr         Loc

Pan322                       

Pan322      Cli         Dha

Pan322      Omn         Sha

;

RUN;

 


Is the text string 'null' in the first row of Inn342 the same as the text string ' ' (empty string) in the first row of Pan322?

--
Paige Miller
SannaSanna
Quartz | Level 8

I was trying to insert blank data for the first row for the two IDs.  The spreadsheet shows blank cell/empty/no data for Inn342 and Pan322 for their most recent record.  I want to pick the next row containing data.  Sorry- I was not able to insert the blank row for each customer in my cards statement.  Can you still  help?  

Kurt_Bremser
Super User

Are we talking about a SAS dataset here, or an Excel spreadsheet?

If it is a SAS dataset, which type are visit1 and site1 (character or numeric)?

SannaSanna
Quartz | Level 8
They are both character in my SAS dataset. (visit1 and site1 contain character data)
Kurt_Bremser
Super User

So then this DATA step will create your data:

data have;
infile datalines dsd truncover;
input ID $ visit1 $ site1 $;
datalines;
Inn342,,
Inn342,Anc,Sha
Inn342,Drr,Loc
Pan322,,
Pan322,Cli,Dha
Pan322,Omn,Sha
;

?

Tom
Super User Tom
Super User

Just use a period as a placeholder in the data line for missing value (numeric or character).

data once;
 input ID $ visit1 $ site1 $  ;
CARDS;
Inn342  .    .
Inn342  Anc  Sha
Inn342  Drr  Loc
Pan322  .    .
Pan322  Cli  Dha
Pan322  Omn  Sha
;

To move the data backwards in time you need make two passes thru the data.

 

You could do it with two separate SET statements.

data want;
  do until(last.id);
    set once;
    by id;
    length first_site $8;
    first_site=coalescec(first_site,site1);
  end;
  do until(last.id);
    set once;
    by id;
    output;
  end;
run;

Or you could merge it with itself.

data want;
  merge once 
        once(keep=id site1 rename=(site1=xxx) where=(not missing(xxx)))
  ;
  by id;
  if first.id then first_site=xxx;
  retain first_site;
  drop xxx;
run;
SannaSanna
Quartz | Level 8
Thank you so much Tom! I tweeked the code a little bit to work with my live data and it worked perfectly. Thank you again so much! Have a wonderful day!
SannaSanna
Quartz | Level 8

Tom,

I used this code: 

data want;
  do until(last.id);
    set once;
    by id;
    length first_site $8;
    first_site=coalescec(first_site,site1);
  end;
  do until(last.id);
    set once;
    by id;
    output;
  end;
run;

 

I was wondering how I could incorporate a county code for the patient to get the same output by county?  The table output would look like this:  patient Inn342 visited two counties with most recent in 01 is Sha and in county 19 is Dha.  

SannaSanna_0-1682630163113.png

 

updated code to include county and blanks. (thank you)

data once;
input ID $ county $ visit1 $ site1 $ ;
CARDS;
Inn342 01 . .
Inn342 01 Anc Sha
Inn342 01 Drr Loc
Pan322 19 . .
Pan322 19 Cli Dha
Pan322 19 Omn Sha
; run;

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 1654 views
  • 0 likes
  • 4 in conversation