The table cannot be opened because it does not contain any columns help

[dchick]

I'm getting this error and I am not sure where I am going wrong. "The table "WORK.MOLDOVA" cannot be opened because it does not contain any columns."

 Thanks!

Here is my code :

 
proc import out=moldova 
datafile='/home/u57403500/ILE/Moldova_data_for_ILE.xlsx' 
dbms=xlsx replace; 
getnames=yes; 
run; 
 
data moldova; 
if aP28=3 then HIVStatus=0; 
else if aP28=2 then HIVStatus=1; 
else if aP28=1 and 4 then HIVStatus=2; 
run; 
 
data moldova; 
infile '/home/u57403500/ILE/Moldova_data_for_ILE.xlsx'; 
input id birth_day MMDDYY8.; 
today = DATE(); 
    days = today - birth_day; 
    age = floor(days / 365);  
    run; 
 
data moldova;  
label aP28='HIV Status' 
	gender_P6='Gender' 
	birth_day='Birthday' 
	P11='Detention' 
	P13_b='Hot Spot' 

[dchick]

There is more to the code it cut it off.

data moldova; 
label aP28='HIV Status'
	gender_P6='Gender'
	birth_day='Birthday'
	P11='Detention'
	P13_b='Hot Spot'
	P21_H='MDR Isoniazid'
	P21_R='MDR Rifampin'
	P21_E='MDR Ethambutol'
	P21_S= 'MDR Streptomycin'
	P21_Z='MDR Pyrazinamide'
	P21_Et='MDR Ethionomide'
	P21_Km='MDR Kanamycin'
	P21_O='MDR Ofloxacin'
	P21_Cs='MDR Cycloserine'
	P21_Ps='MDR p-Aminosalicylic acid';
run;

data moldova;
MDR=P21_H+P21_R+P21_E+P21_S+P21_Z+P21_Et+P21_Km+P21_O+P21_Cs+P21_Ps;
HIV=ap28;
Hotspot=p13_b;
Detention=p11;
gender=gender_P6;
run; 

data moldova;
proc format;
value HIVf 0='HIV Negative' 1='HIV Positive' 2='Unknown';
value genderf 1='Male' 2='Female';
value detentionf 1='Yes' 2='No';
value Hotspotf 1='Yes' 2='No';
value MDR 1='Resistant' 2='Other'
format HIV HIVf. gender genderf.
detention detentionf. Hotspot Hotspotf. ;
run;

[Reeza]

*imports data from XLSX file;
proc import out=moldova 
datafile='/home/u57403500/ILE/Moldova_data_for_ILE.xlsx' 
dbms=xlsx replace; 
getnames=yes; 
run; 
 

*create formats for later usage;
proc format;
value HIVf 0='HIV Negative' 1='HIV Positive' 2='Unknown';
value genderf 1='Male' 2='Female';
value detentionf 1='Yes' 2='No';
value Hotspotf 1='Yes' 2='No';
value MDR 1='Resistant' 2='Other';

run;

data moldova2; *creates new data set called moldova2;
set moldova; *uses input from XLSX file called moldova;

*create HIV Status variable;
if aP28=3 then HIVStatus=0; 
else if aP28=2 then HIVStatus=1; 
else if aP28=1 and 4 then HIVStatus=2; 


*label variables;
label aP28='HIV Status'
	gender_P6='Gender'
	birth_day='Birthday'
	P11='Detention'
	P13_b='Hot Spot'
	P21_H='MDR Isoniazid'
	P21_R='MDR Rifampin'
	P21_E='MDR Ethambutol'
	P21_S= 'MDR Streptomycin'
	P21_Z='MDR Pyrazinamide'
	P21_Et='MDR Ethionomide'
	P21_Km='MDR Kanamycin'
	P21_O='MDR Ofloxacin'
	P21_Cs='MDR Cycloserine'
	P21_Ps='MDR p-Aminosalicylic acid';

*no idea what these do;
MDR=P21_H+P21_R+P21_E+P21_S+P21_Z+P21_Et+P21_Km+P21_O+P21_Cs+P21_Ps;
HIV=ap28;
Hotspot=p13_b;
Detention=p11;
gender=gender_P6;

*applies formats;
format HIV HIVf. gender genderf.
detention detentionf. Hotspot Hotspotf. ;

run; 

proc print data=moldova2(obs=10) label;
run;



 

This should get you started - I'm fairly certain its not all correct but I don't have the file. 

---

@dchick wrote:

I'm getting this error and I am not sure where I am going wrong. "The table "WORK.MOLDOVA" cannot be opened because it does not contain any columns."

 Thanks!

Here is my code :

 
proc import out=moldova 
datafile='/home/u57403500/ILE/Moldova_data_for_ILE.xlsx' 
dbms=xlsx replace; 
getnames=yes; 
run; 
 
data moldova; 
if aP28=3 then HIVStatus=0; 
else if aP28=2 then HIVStatus=1; 
else if aP28=1 and 4 then HIVStatus=2; 
run; 
 
data moldova; 
infile '/home/u57403500/ILE/Moldova_data_for_ILE.xlsx'; 
input id birth_day MMDDYY8.; 
today = DATE(); 
    days = today - birth_day; 
    age = floor(days / 365);  
    run; 
 
data moldova;  
label aP28='HIV Status' 
	gender_P6='Gender' 
	birth_day='Birthday' 
	P11='Detention' 
	P13_b='Hot Spot' 

---

[dchick]

This definitely helps! Thank you!
Where you said you have no idea what those do was my attempt to combine variables and rename them. One last question where would I place the age statement?

infile '/home/u57403500/ILE/Moldova_data_for_ILE.xlsx';
input id birth_day MMDDYY8.;
today = DATE();
    days = today - birth_day;
    age = floor(days / 365); 

[Tom]

Why do you keep re-creating the same dataset over and over?

This step will not create any real data

data moldova; 
if aP28=3 then HIVStatus=0; 
else if aP28=2 then HIVStatus=1; 
else if aP28=1 and 4 then HIVStatus=2; 
run; 

because you don't have any input data.  Instead since you are referencing two variables they will be created and set to missing.  So none of your IF statements can be true.  Even the last one which is really just a test whether AP28 is 1 since the AND operator will treat the constant 4 as a TRUE value.

This one cannot work because SAS will not be able to read a binary file such as an XLSX file as if it was a text file.

data moldova; 
infile '/home/u57403500/ILE/Moldova_data_for_ILE.xlsx'; 
input id birth_day MMDDYY8.; 
today = DATE(); 
    days = today - birth_day; 
    age = floor(days / 365);  
run; 

[dchick]

Thank you! How would you create an age variable from a birthday (MMDDYYYY)?

[Reeza]

Move the code to the data step.