Variable types

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,228
Accepted Solution

Variable types


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.


Accepted Solutions
Solution
‎05-05-2014 09:47 PM
Super Contributor
Posts: 644

Re: Variable types

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


All Replies
Super User
Posts: 11,343

Re: Variable types

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.

Super Contributor
Posts: 644

Re: Variable types

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

Trusted Advisor
Posts: 1,228

Re: Variable types

Posted in reply to RichardinOz

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

Solution
‎05-05-2014 09:47 PM
Super Contributor
Posts: 644

Re: Variable types

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

Trusted Advisor
Posts: 1,228

Re: Variable types

Posted in reply to RichardinOz

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.

Super Contributor
Posts: 644

Re: Variable types

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

Trusted Advisor
Posts: 1,228

Re: Variable types

Posted in reply to RichardinOz

Thanks Richard - working perfectly.

Naeem

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 268 views
  • 6 likes
  • 3 in conversation