Hi Experts,
I've a numer of datasets (30 approx). with a variable var7. Variable var7 (actually numeric) is appearing as numeric in some of the datasets and character in the remaining datasets. Is there a simple way to make it numeric in all of the datasets?
Thanks in advance for your help.
Here is something to try: inserting that row of zeros in the CSV file before importing to SAS. You could wrap this in a macro and run the macro for each file. Assuming the first column is a character record id
Filename csvfile "insert fully referenced csv filename here" ;
Filename csvfile2 "insert another fully referenced csv filename here" ; /* for modified csv input */
Data _Null_ ;
Infile csvfile ; /* you might need a lrecl= option here */
If _N_ = 1 then
do ;
Put _Infile_ ;
Put "Dummy, 0, 0, 0, 0, 0, 0, 0" ;
end ;
Else Put _Infile_ ;
Run ;
Proc import ... ; /* use csvfile2 for import */
run ;
Data have ;
Set have (firstobs = 2) ;
Run;
Richard
Would I win any money betting that these data sets were built using Proc Import from Excel?
You'll not be able to "change" the type in the existing data set. You might create a new variable using a data step and input on the old variable and overwrite your existing set with the new:
Data dataset1;
set dataset1;
NewVar7 = input(var7, best8.); /* or if the values are longer then bestx.*/
run;
However you may get warning messages for the sets var7 is alread numeric.
Or go back and control how the original data is created.
If you want to keep the name var7 do this:
Data dataset1;
set dataset1 (rename = (var7 = OldVar7)) ;
Var7 = input(OldVar7, ?? best.); /* specify length if any number has more than 12 significant digits */
Drop OldVar7 ;
run;
If, as ballardw guesses, these files are coming from Excel this problem can occur if there are a series of blank cells for the column in the first few records. One palliative is to insert an additional dummy row ( line 2) in Excel, consisting of zeros, to force the type recognition. You can then drop the row in SAS using the dataset option firstobs=2.d
Richard
Message was edited by: Richard Carson Added the ?? modifier to suppress warning messages in Import() .
Thanks ballardW solution and Richard for your input on this. Yes, that is correct I imported csv files and having problems in manipulating variables. Var7 is supposed to be numeric in my all datasets but I don't know what went wrong while importing files, may be I missed something. Now I am looking for some macro that can add all the datasets and fix var7 at the same time.
Regards,
Naeem
Here is something to try: inserting that row of zeros in the CSV file before importing to SAS. You could wrap this in a macro and run the macro for each file. Assuming the first column is a character record id
Filename csvfile "insert fully referenced csv filename here" ;
Filename csvfile2 "insert another fully referenced csv filename here" ; /* for modified csv input */
Data _Null_ ;
Infile csvfile ; /* you might need a lrecl= option here */
If _N_ = 1 then
do ;
Put _Infile_ ;
Put "Dummy, 0, 0, 0, 0, 0, 0, 0" ;
end ;
Else Put _Infile_ ;
Run ;
Proc import ... ; /* use csvfile2 for import */
run ;
Data have ;
Set have (firstobs = 2) ;
Run;
Richard
Hi Richard,
I applied the syntax above and getting following message. Any suggestions please.
Regards,
Naeem
NOTE: The infile CSVFILE is:
Filename=C:\Users\Awan\Desktop\file1.csv,
RECFM=V,LRECL=256,File Size (bytes)=26742808,
Last Modified=April 30, 2014 12:04:32 o'cloc,
Create Time=May 06, 2014 14:48:49 o'clock
ERROR: Put _INFILE_ executed before INPUT statement for INFILE
C:\Users\Awan\Desktop\file1.csv.
My bad - I do not have a SAS implementation at home to test code. This may fix the problem
Data _Null_ ;
Infile csvfile ; /* you might need a lrecl= option here */
File csvfile2 ;
Input ;
If _N_ = 1 then
do ;
Put _Infile_ ;
Put "Dummy, 0, 0, 0, 0, 0, 0, 0" ;
end ;
Else Put _Infile_ ;
Run ;
Proc import ... ; /* use csvfile2 for import */
run ;
Data have ;
Set have (firstobs = 2) ;
Run;
Richard
Thanks Richard - working perfectly.
Naeem
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.