topic Re: First but not Blank in SAS Procedures

First but not Blank

SannaSanna — Thu, 27 Apr 2023 19:04:41 GMT

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 .

DATA Once;

INPUT ID $ visit1 site1 ;

CARDS;

Inn342 null null

Inn342 Anc Sha

Inn342 Drr Loc

Pan322

Pan322 Cli Dha

Pan322 Omn Sha

;

RUN;

Re: First but not Blank

Kurt_Bremser — Thu, 27 Apr 2023 19:10:46 GMT

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.

Re: First but not Blank

PaigeMiller — Thu, 27 Apr 2023 19:12:21 GMT

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

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?

Re: First but not Blank

SannaSanna — Thu, 27 Apr 2023 19:21:40 GMT

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?

Re: First but not Blank

Kurt_Bremser — Thu, 27 Apr 2023 19:29:35 GMT

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

Re: First but not Blank

SannaSanna — Thu, 27 Apr 2023 19:33:09 GMT

They are both character in my SAS dataset. (visit1 and site1 contain character data)

Re: First but not Blank

Kurt_Bremser — Thu, 27 Apr 2023 19:38:17 GMT

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
;

Re: First but not Blank

Tom — Thu, 27 Apr 2023 19:57:24 GMT

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;

Re: First but not Blank

SannaSanna — Thu, 27 Apr 2023 20:54:04 GMT

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!

Re: First but not Blank

SannaSanna — Thu, 27 Apr 2023 21:18:35 GMT

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.

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;