topic Re: missing my observations in SAS Programming

missing my observations

stancemcgraw — Thu, 31 Mar 2016 02:08:18 GMT

I input the following code in order to merge some tables together, but then it says I have 37 variables and zero obervations. Where did all my observations go?

WARNING: Multiple lengths were specified for the variable TICI_Scale by input data set(s). This may cause truncation of data.

NOTE: There were 380 observations read from the data set IAT.TIMETOIAT.

NOTE: There were 2767 observations read from the data set IAT.NB1213D.

NOTE: There were 1410 observations read from the data set IAT.NB14B.

NOTE: There were 397 observations read from the data set IAT.NB15B.

NOTE: The data set IAT.TIMETOIAT4 has 0 observations and 37 variables.

NOTE: DATA statement used (Total process time):

real time 0.11 seconds

cpu time 0.06 seconds

libname Iat '\\vmware-host\Shared Folders\Desktop\SAS\IAT\';
 
 
  
 proc import datafile="Z:\Desktop\SAS\IAT\timetoiat"
 out=Iat.timetoiat dbms=xlsx replace;
 sheet="Sheet1";
 getnames=yes;
 
  run;
 
 
proc import datafile="Z:\Desktop\OneDrive\Documents\Trauma-Research\Time to IAT Study\2012 and 2013 data for research, cleaned.csv"
out=Iat.NB1213 dbms=csv replace; 
guessingrows=4000;
getnames=yes; 
run;
 
 
data iat.nb1213b; 
set iat.nb1213 (keep= stroke_id nihssdc mrdc dcdisp iatype nihssinitial); 
retain stroke_id2;
 
if stroke_id = '' 
and nihssdc = . 
and mrdc = . 
and dcdisp = '' 
and iatype = '' 
and nihssinitial = . 
    then delete;
 
drop stroke_id2;
 
if stroke_id NE '' then stroke_id2 = stroke_id;
else stroke_id = stroke_id2;
run;
 
 
proc sort data = iat.nb1213b; 
by stroke_id; 
run;

proc transpose data = iat.nb1213b out=iat.nb1213c (drop=_name_) prefix=iatype;
by stroke_id;
var iatype;
run;
 
  
data iat.nb1213d; 
merge iat.nb1213b (in=test) iat.nb1213c; 
by stroke_id; 
drop iatype;
 
if first.stroke_id then output;
 
run;
 
proc import datafile="Z:\Desktop\OneDrive\Documents\Trauma-Research\Time to IAT Study\2014 Neurobase data cleaned.csv"
out=Iat.NB14 dbms=csv replace; 
guessingrows=4000;
getnames=yes; 
run;
 
data iat.nb14b; 
set iat.nb14 (keep=  stroke_id nihssdc mrdc dcdisp TICI_scale CTfirstslicetoneedle groinpuncturetorecantime symptom_arrival nihssinitial); 
retain stroke_id2;
 
if stroke_id = '' 
and nihssdc = . 
and mrdc = . 
and dcdisp = '' 
and TICI_scale = '' 
and CTfirstslicetoneedle = . 
and groinpuncturetorecantime = . 
and symptom_arrival = .
and nihssinitial = . 
    then delete;
 
drop stroke_id2;
 
if stroke_id NE '' then stroke_id2 = stroke_id;
else stroke_id = stroke_id2;
 
run;

proc sort data = iat.nb14b; 
by stroke_id; 
run; 

data iat.nb1213d; 
merge iat.nb1213b (in=test)  iat.nb1213c; 
by stroke_id; 
drop iatype;
if first.stroke_id then output;
 
run;

proc import datafile="Z:\Desktop\OneDrive\Documents\Trauma-Research\Time to IAT Study\2015 Neurobase data cleaned.csv"
out=Iat.NB15 dbms=csv replace; 
guessingrows=4000;
getnames=yes; 
run;
 
data iat.nb15b; 
set iat.nb15 (keep=  stroke_id nihssdc nihssinitial mrdc dcdisp TICI_scale CTfirstslicetoneedle groinpuncturetorecantime symptom_arrival); 
retain stroke_id2;
 
if stroke_id = '' 
and nihssdc = . 
and mrdc = . 
and dcdisp = '' 
and TICI_scale = '' 
and CTfirstslicetoneedle = . 
and groinpuncturetorecantime = . 
and symptom_arrival = . 
and nihssinitial = . 
    then delete;

drop stroke_id2;
 
if stroke_id NE '' then stroke_id2 = stroke_id;
else stroke_id = stroke_id2;
run;
 
proc sort data = iat.nb15b; by stroke_id; run;
proc sort data = iat.nb14b; by stroke_id; run;
proc sort data = iat.nb1213d; by stroke_id; run;
proc sort data=iat.timetoiat; by strokeid;run;
 
data iat.timetoiat4; 
merge iat.timetoiat (in=keep rename=(strokeid=stroke_id)) iat.nb1213d iat.nb14b iat.nb15b;
by stroke_id;

if keep2 NE 1 then delete;
else keep2=keep;
run;

Re: missing my observations

Reeza — Thu, 31 Mar 2016 02:11:16 GMT

Sorry - it appears the browser unformats the code.

In your code you're using a variable KEEP2 but it doesn't appear to have been created anywhere. Did you mean to reference KEEP, from IN=KEEP?

Also, since KEEP is a keyword in SAS I would recommend using a different name.

Re: missing my observations

stancemcgraw — Thu, 31 Mar 2016 02:39:50 GMT

I tried to switch out keep and it still did not work

Re: missing my observations

Reeza — Thu, 31 Mar 2016 03:02:09 GMT

What happens if you run the following? Please include the log if you get errors - the full log including the code.

data iat.timetoiat4; 
merge iat.timetoiat (rename=(strokeid=stroke_id)) 
iat.nb1213d 
iat.nb14b
iat.nb15b;

by stroke_id;

run;

Re: missing my observations

Reeza — Thu, 31 Mar 2016 03:04:17 GMT

Also, what are you expecting to happen here:

drop stroke_id2;
 
if stroke_id NE '' then stroke_id2 = stroke_id;
else stroke_id = stroke_id2;

Since you drop stroke_id2 then it's the equivalent of:

drop stroke_id2;
 
if stroke_id eq '' then stroke_id = stroke_id2;

Re: missing my observations

stancemcgraw — Thu, 31 Mar 2016 03:06:01 GMT

I don't get errors, but it does not read in all the data i need from the table timeiat. There is a lot missing

WARNING: Multiple lengths were specified for the variable TICI_Scale by input data set(s). This may cause truncation of data.

NOTE: There were 380 observations read from the data set IAT.TIMETOIAT.

NOTE: There were 2767 observations read from the data set IAT.NB1213D.

NOTE: There were 1410 observations read from the data set IAT.NB14B.

NOTE: There were 397 observations read from the data set IAT.NB15B.

NOTE: The data set IAT.TIMETOIAT4 has 4652 observations and 36 variables.

NOTE: DATA statement used (Total process time):

real time 0.09 seconds

cpu time 0.07 seconds

Re: missing my observations

Reeza — Thu, 31 Mar 2016 03:12:12 GMT

This implies it's not a technical error - it's a logical error.

Unfortunately this means we can only guess and that you need to figure out what's going on. I also don't know what you mean by missing here. Are you missing rows, or missing values for a specific variable? Missing variables entirely?

Ideally you post data that replicates your issue, but with 4 datasets it gets cumbersome.

Here are some idea's here on how to check that your merge is happening correctly.

https://communities.sas.com/t5/Base-SAS-Programming/How-to-check-if-merge-happened-correctly-in-sas-and-things-to/m-p/259755/highlight/true#M50291

Some things I would try:

1. Verify all the input datasets are the way you want

2. Start by merging two data sets, does this give you what you want? If not, what do you need to change.

Re: missing my observations

stancemcgraw — Thu, 31 Mar 2016 03:13:30 GMT

I'm missing ots of observations for variables, but not variables

Re: missing my observations

Reeza — Thu, 31 Mar 2016 03:22:54 GMT

You'll have to trace the data to figure out why they're missing.

Here's one way to start doing it, add in an indicator to determine which records come from where. Identify one of your 'missing' observations and trace back from which data set is missing.

Is your assumption that all data should be in all 4 datasets or will some datasets have some ID's and others have some? Also, I'm assuming that you're not doing a many to many merge - ie you have multiple records for your BY variable in any two of your datasets.

Beyond this, without data, I can't help.

Good Luck.

data iat.timetoiat4; 
merge iat.timetoiat (rename=(strokeid=stroke_id) in=D1) 
iat.nb1213d (in=D2)
iat.nb14b (in=D3)
iat.nb15b (in=D4);

by stroke_id;

SD1=D1;
SD2=D2;
SD3=D3;
SD4=D4;

run;

Re: missing my observations

Reeza — Thu, 31 Mar 2016 03:56:31 GMT

Also, run a proc contents on each input dataset and make sure the length type for the merge variable is the same.

Re: missing my observations

ballardw — Thu, 31 Mar 2016 06:52:52 GMT

data iat.timetoiat4; 
   merge iat.timetoiat (in=keep rename=(strokeid=stroke_id)) 
   iat.nb1213d 
   iat.nb14b 
   iat.nb15b;
   by stroke_id;

if keep2 NE 1 then delete;
else keep2=keep;

run;

If you do not have a variable named KEEP2 prior to here then the variabl is created with a missing value. Since missing is ALWAYS ne 1 then every record is deleted.