Missing data when importing data from individual hospitals then aggregating the data

[mayasak]

Hi,

I have several hospital files that have tables that include data about tests performed for organisms and drug combinations such as the number of tests performed and susceptibility percentage.

I use the code below to extract the hospital names into a separate variable in the filename dataset;

data filename_hospital;
set filenames;
basefile_cap = upcase(basefile);
HOSPITAL_drop = substr(basefile_cap, 1, (index(basefile_cap, 'ANTI')-1));
HOSPITAL = compress(HOSPITAL_drop,'_');
FILENAME = pathname;
run;

and the following call executes to call the Gram Negative data transform macros to pass through the filename and hospital parameters from the filename dataset created above;

data _null_;
set filename_hospital;
call execute('%gn_transform(FILENAME='||FILENAME||', HOSPITAL='||HOSPITAL||');');
run;

All hospitals' information is extracted except for the "Children's Hospital" which has the data for this hospital but the name of the hospital field is missing.

Thank you 

[ballardw]

Is the hospital with the problem name actually in the data set named Filenames? It isn't clear and that sort of data sometimes has problems reading depending on how the file was read.

I think that you need to show the complete value of the variable Basefile for that observation.

For one thing expect problems if there is no part of the variable that is ANTI .

You might also show us the LOG from when you run the code. Copy the log including the code and all messages generated by that data step. Then on the forum open a text box using the </> icon and paste the text. The text box will preserve the formatting of any diagnostic text and sets it apart visually from the discussion text.

[Tom]

I cannot tell from the way you worded the question the exact value that is in the variable.

If you literally have this value in HOSPITAL

Children's Hospital

Then this statement:

call execute('%gn_transform(FILENAME='||FILENAME||', HOSPITAL='||HOSPITAL||');');

Is going to pass the following string to CALL EXECUTE

%gn_transform(FILENAME=somefilename,HOSPITAL=Children's Hospital);

and that is a problem because the string contains unbalanced quotes.

You could try adding macro quoting so that the value at least makes it into the macro call.  (Also wrap the macro call in %nrstr() to delay calling the macro until the string is pull back off the stack to begin executing.)

call execute(cats('%nrstr(%gn_transform)(FILENAME=',FILENAME,', HOSPITAL=%nrstr(%bquote(',HOSPITAL,'));')));

Which should show line this on the SAS log 

+ %gn_transform(FILENAME=somefilename,HOSPITAL=%bquote(Children's Hospital));

But that might cause trouble for the macro, depending on how it using the HOSPITAL macro variable.

So perhaps it would just be easier to modify the macro to expect the value of HOSPITAL to already include the quotes.  Then the calling statement can just look like:

call execute(cats('%nrstr(%gn_transform)(FILENAME=',FILENAME,', HOSPITAL=',quote(trim(HOSPITAL)),');'));

And now the SAS log will show this instead:

+ %gn_transform(FILENAME=somefilename,HOSPITAL="Children's Hospital");