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


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.

1 ACCEPTED SOLUTION

Accepted Solutions
RichardinOz
Quartz | Level 8

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

View solution in original post

7 REPLIES 7
ballardw
Super User

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.

RichardinOz
Quartz | Level 8

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() .

stat_sas
Ammonite | Level 13

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

RichardinOz
Quartz | Level 8

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

stat_sas
Ammonite | Level 13

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.

RichardinOz
Quartz | Level 8

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

stat_sas
Ammonite | Level 13

Thanks Richard - working perfectly.

Naeem

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!

How to Concatenate Values

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.

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
  • 7 replies
  • 677 views
  • 6 likes
  • 3 in conversation