Input code to change variable to numeric doesn't work in enterprise guide

[chelseah]

I’m at a loss. The code below won't work in Enterprise Guide but will in Base Sas. Also, I have the same input code for a different variable earlier in the program that works in both environments. The problem input code is in blue below.

I'm using Enterprise Guide 6.1. Things I've tried: changing the format to comma12., best12., and dollar12., running the specific portion of the code in a different enterprise window, changing the original dataset's variable's name, moved the input code to various different places in the program, restarted the program, and restarted computer. Solution:  had a coworker run the code without any problems- and it worked like a charm. She uses Base SAS 9.4 and I was then able to run it in that program with no problems as well.

Problem: I need to convert ‘Annual_Retail_Sales_17a’ to a numeric variable but when I run this code it creates two numeric variables (Annual_Retail_Sales_17a and Annualretailsales), both missing all values. And the original character variable is still there (so I will have three variables, but two with missing values, and the original has values but is still characters)!

title "open SAS file";

data  FNS (keep=  food_stamp_ID contract_id vendor_id);

set 'X:\research\projects\pg(CHC)\STARS data\fnslist201711.sas7bdat';

run;

data FNS;

set FNS;

food_stamp_id2 = input (food_stamp_id, 10.);

drop food_stamp_id;

rename food_stamp_id2=food_stamp_id;

run;

title "import csv file";

proc import datafile="X:\research\projects\pg(CHC)\ss\Sample 1 2 SF v2.csv "

out= work.Sample

dbms=csv replace ;

getnames=YES;

run;

proc sort data=fns ; by food_stamp_id; run;

proc sort data=sample ; by food_stamp_id; run;

data FNSSample;

merge sample (in=first) fns;

by food_stamp_id;

if first;

run;

data fnssample;

set fnsSample;

AnnualRetailSales = input ( Annual_Retail_Sales_17a, comma12.);

run;

[Astounding]

Check the spelling of the names, particularly the annual retail sales.  You are using different names in different places.  Most likely Annual_Retail_Sales_17a is an incorrect spelling for the name of the existing character variable.

[chelseah]

I wish that was the case 😞 I copied and pasted the variable name to avoid that problem. And the code works without any changes and without any problems in Base SAS. 

[Reeza]

1. Never code like this:

data FNS;

set FNS;

food_stamp_id2 = input (food_stamp_id, 10.);

drop food_stamp_id;

rename food_stamp_id2=food_stamp_id;

run;

That will destroy your original data and you cannot run that step independently, you will need to reread the original data again.

libname mydata 'X:\research\projects\pg(CHC)\STARS data\';

title "open SAS file";
data  FNS (keep=  food_stamp_ID contract_id vendor_id);
set mydata.fnslist201711 (rename=food_stamp_Id = _old_id);

food_stamp_id =input(_old_id, 10.);

drop _old_id;

run;


proc sort data=fns ;
by food_stamp_id; 
run;

Please test the code above and show the log as well. 

If the conversion doesn't work, please run a proc contents on the  FNS data set and post the output here.

[chelseah]

I have libnames already established earlier in my program, but you're right! I did miss that error. Luckily in this situation it was for the better, but that could have been really bad. Thanks for looking out though! That would be a very serious problem indeed 🙂

[ballardw · Accepted solution]

I would suggest starting by changing:

title "import csv file";
proc import datafile="X:\research\projects\pg(CHC)\ss\Sample 1 2 SF v2.csv "
out= work.Sample
dbms=csv replace ;
getnames=YES;
run;

to

title "import csv file";
proc import datafile="X:\research\projects\pg(CHC)\ss\Sample 1 2 SF v2.csv "
out= work.Sample
dbms=csv replace ;
getnames=YES;
guessingrows=max;
run;

Which will look at more rows before "guessing" whether a variable should be numeric or character and setting lengths of character variables.

If you are going to read more than one csv file in this format I would suggest looking at the log after running the proc import, copy the data step code used to read your CSV file, save that in the editor and modify informats to match data as needed. Then next time you read a CSV in that format just change the infile and data set name and all the characteristics will match. Change variable names if desired.

Since you say that you get two numeric variables, Annual_Retail_Sales_17a and Annualretailsales then it is likely that

AnnualRetailSales = input ( Annual_Retail_Sales_17a, comma12.);

does not actually reference an existing variable that is character. It may be what you think is Annual_Retail_Sales_17a has two _ in some part of the name and you only typed one for the code. I would guess the most likely place would be before the 17. Double check the actual spelling of your existing variable and use that.

Or use a modified data step as suggested above to read the file when creating work.sample which should create your AnnualRetailSales directly if done properly.

[chelseah]

While the 'guessingrows=max' did not solve my problem, I was able to grab the code from the log and change my variables that way. I always forget that little trick. I still don't know why the exact code works in base sas and not in enterprise, but I'm surrendering and moving on. Maybe one day I will find a solution. Thank you for all that chimed in!