BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chelseah
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

6 REPLIES 6
Astounding
PROC Star

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6
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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1264 views
  • 1 like
  • 4 in conversation