topic Import CSV to CAS via the web interface does not detect dates in SAS Studio

Import CSV to CAS via the web interface does not detect dates

Aiman — Fri, 24 Apr 2020 22:39:44 GMT

Hi Im importing csv file to via the web interface but SAS recognized my date column as VARCHAR not as Date type. Can someone assist me or provide something to read/ learn on how to import Date type of data to the environment.

Re: Import CSV to CAS via the web interface does not detect dates

Shmuel — Sat, 25 Apr 2020 01:31:14 GMT

Please post a sample lines of your csv file to show the exact format of date input.

In general, in case like you described, read the data into a temporary char type variable then use function input to convert it to a numeric variable with appropriate format as in next example:

datex = "03DEC2020:00:00:00";

date = input(datex,daytime18.);

Re: Import CSV to CAS via the web interface does not detect dates

Aiman — Sun, 26 Apr 2020 10:33:03 GMT

Hi thank you for your response. unfortunately an error occur while I try to run the code. What should I do to resolve the issue? Thank you in advance

/* Generated Code (IMPORT) */
/* Source File: corrected_csv.csv */
/* Source Path: /Users/maiman.khalid/My Folder/corrected_csv.csv */
/* Code generated on: Apr 26, 2020, 6:28:02 PM */

proc sql;
%if %sysfunc(exist(WORK.IMPORT)) %then %do;
drop table WORK.IMPORT;
%end;
%if %sysfunc(exist(WORK.IMPORT,VIEW)) %then %do;
drop view WORK.IMPORT;
%end;
quit;

FILENAME REFFILE FILESRVC FOLDERPATH='/Users/maiman.khalid/My Folder' FILENAME='corrected_csv.csv';

PROC IMPORT DATAFILE=REFFILE
DBMS=CSV
OUT=WORK.IMPORT;
GETNAMES=YES;
RUN;

PROC CONTENTS DATA=WORK.IMPORT;
date = input(Date,date.9);
RUN;

Re: Import CSV to CAS via the web interface does not detect dates

Shmuel — Sun, 26 Apr 2020 12:39:27 GMT

The line

date = input(Date,date.9);

cannot be used with a procedure but in a sas data step only.

after running proc contents:

PROC CONTENTS DATA=WORK.IMPORT; run;

check what type is date variable - is it CHAR type or NUM type.

Please post an example of a date value you got. You can do it by

proc print data=work.import(obs=3); /* 3 observations are enough */
var date; /* add any variable you want to reformat */
run;

the next step depends on type and values of your imported dataset.

Re: Import CSV to CAS via the web interface does not detect dates

Aiman — Fri, 08 May 2020 01:48:56 GMT

cas mysess sessopts= (caslib=casuser);
libname test cas caslib=casuser;

proc print data=test.datauncertaintytest(obs=3);
var Date;
run;

data datauncertaintytestnew; 
set test.datauncertaintytest; 
date = input(Date,date13.); 
run;

This is the code that I have run, but I still can't manage to convert it to date type. Below is the warning message appear whenever I try to run the code.

1    %studio_hide_wrapper;
82   data datauncertaintytestnew;
83   set test.datauncertaintytest;
84   date = input(Date,date13.);
85   run;
NOTE: Numeric values have been converted to varchar values at the places given by: (Line):(Column).
      84:8   
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with 
      options MSGLEVEL=I.
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=56.34672546 Standard Volume Flowrate=105.4290771 Density=929.2802734 Pressure=10.06341743 Temperature=37.31236649
Mass Flowrate=99.33228302 Standard Density=941.8872681 Gross Volume Flowrate=106.9438782 Pressure UN=2.493242741
Temperature UN=1.523893118 Density UN=0.365905255 Standard Density UN=0.371473104 Mass Flow UN=0.095913514
Gross Volume UN=0.378267318 Standard Volume UN=0.386022657 Nett Volume UN=0.988607407 Un CPL=0.000347 Un CTL=0.000701 _ERROR_=1
_N_=1
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=38.45364761 Standard Volume Flowrate=192.9372559 Density=900.4077759 Pressure=9.544417381 Temperature=37.31245804
Mass Flowrate=176.533905 Standard Density=914.9498901 Gross Volume Flowrate=195.786026 Pressure UN=2.647840261
Temperature UN=1.523890972 Density UN=0.377639294 Standard Density UN=0.383303821 Mass Flow UN=0.092314333
Gross Volume UN=0.388758779 Standard Volume UN=0.396551251 Nett Volume UN=0.997150302 Un CPL=0.000347 Un CTL=0.000685 _ERROR_=1
_N_=2
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=60.38313293 Standard Volume Flowrate=78.46936798 Density=934.6755371 Pressure=8.741588593 Temperature=37.31254959
Mass Flowrate=74.43052673 Standard Density=948.5812988 Gross Volume Flowrate=79.64730835 Pressure UN=2.899116755
Temperature UN=1.523888946 Density UN=0.363791853 Standard Density UN=0.369319737 Mass Flow UN=0.086805478
Gross Volume UN=0.374005616 Standard Volume UN=0.381805271 Nett Volume UN=0.986186683 Un CPL=0.000347 Un CTL=0.000691 _ERROR_=1
_N_=3
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=51.1260643 Standard Volume Flowrate=117.0168152 Density=921.1083984 Pressure=9.106567383 Temperature=37.31264114
Mass Flowrate=109.2582016 Standard Density=933.6972656 Gross Volume Flowrate=118.668129 Pressure UN=2.747273684
Temperature UN=1.523886919 Density UN=0.369149655 Standard Density UN=0.374710709 Mass Flow UN=0.089095213
Gross Volume UN=0.379749179 Standard Volume UN=0.387541175 Nett Volume UN=0.990406036 Un CPL=0.000347 Un CTL=0.00068 _ERROR_=1 _N_=4
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=66.97434235 Standard Volume Flowrate=113.9334335 Density=945.4837036 Pressure=8.81932354 Temperature=37.31272888
Mass Flowrate=109.1727753 Standard Density=958.2043457 Gross Volume Flowrate=115.6290894 Pressure UN=2.842592478
Temperature UN=1.523884773 Density UN=0.359632432 Standard Density UN=0.365106761 Mass Flow UN=0.087156072
Gross Volume UN=0.370042801 Standard Volume UN=0.377841502 Nett Volume UN=0.98312974 Un CPL=0.000347 Un CTL=0.000675 _ERROR_=1 _N_=5
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=75.88699341 Standard Volume Flowrate=97.13356018 Density=959.2821655 Pressure=8.482422829 Temperature=37.31282043
Mass Flowrate=94.29468536 Standard Density=972.6743774 Gross Volume Flowrate=98.54508972 Pressure UN=2.954531431
Temperature UN=1.523882747 Density UN=0.35445866 Standard Density UN=0.359887362 Mass Flow UN=0.084882446
Gross Volume UN=0.364480406 Standard Volume UN=0.372295558 Nett Volume UN=0.979126871 Un CPL=0.000347 Un CTL=0.000709 _ERROR_=1
_N_=6
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=28.85190582 Standard Volume Flowrate=200.5797577 Density=886.34729 Pressure=8.545556068 Temperature=37.31291199
Mass Flowrate=180.2424927 Standard Density=897.1813965 Gross Volume Flowrate=203.8719482 Pressure UN=2.932470083
Temperature UN=1.52388072 Density UN=0.383625448 Standard Density UN=0.389287621 Mass Flow UN=0.085302599 Gross Volume UN=0.39299497
Standard Volume UN=0.400836915 Nett Volume UN=1.001140952 Un CPL=0.000347 Un CTL=0.000672 _ERROR_=1 _N_=7
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=81.05840302 Standard Volume Flowrate=94.07204437 Density=967.7349243 Pressure=8.946340561 Temperature=37.31300354
Mass Flowrate=92.16530609 Standard Density=979.7310791 Gross Volume Flowrate=95.43640137 Pressure UN=2.795284986
Temperature UN=1.523878574 Density UN=0.351361573 Standard Density UN=0.356751233 Mass Flow UN=0.08798559
Gross Volume UN=0.362210453 Standard Volume UN=0.370015234 Nett Volume UN=0.977144122 Un CPL=0.000347 Un CTL=0.000668 _ERROR_=1
_N_=8
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=88.68231964 Standard Volume Flowrate=98.42247772 Density=978.8284912 Pressure=8.931808472 Temperature=37.31309509
Mass Flowrate=97.44003296 Standard Density=994.3106079 Gross Volume Flowrate=99.83333588 Pressure UN=2.799768686
Temperature UN=1.523876548 Density UN=0.34737891 Standard Density UN=0.352737159 Mass Flow UN=0.087885648
Gross Volume UN=0.358323872 Standard Volume UN=0.36613822 Nett Volume UN=0.974255502 Un CPL=0.000347 Un CTL=0.000677 _ERROR_=1 _N_=9
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=72.90836334 Standard Volume Flowrate=81.76197815 Density=954.4538574 Pressure=8.980819702 Temperature=37.31318665
Mass Flowrate=79.21022797 Standard Density=968.7826538 Gross Volume Flowrate=82.96118164 Pressure UN=2.784508705
Temperature UN=1.523874521 Density UN=0.356249958 Standard Density UN=0.361648828 Mass Flow UN=0.08822193 Gross Volume UN=0.3670111
Standard Volume UN=0.374807268 Nett Volume UN=0.980733871 Un CPL=0.000347 Un CTL=0.000693 _ERROR_=1 _N_=10
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=49.48620605 Standard Volume Flowrate=194.749588 Density=917.7335815 Pressure=9.047511101 Temperature=37.3132782
Mass Flowrate=181.2253723 Standard Density=930.5213013 Gross Volume Flowrate=197.6847687 Pressure UN=2.764283895
Temperature UN=1.523872375 Density UN=0.370503426 Standard Density UN=0.375994742 Mass Flow UN=0.088682577
Gross Volume UN=0.380969018 Standard Volume UN=0.38876462 Nett Volume UN=0.991390526 Un CPL=0.000347 Un CTL=0.000688 _ERROR_=1
_N_=11
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=56.31933975 Standard Volume Flowrate=96.3243866 Density=928.2366333 Pressure=9.452088356 Temperature=37.31336975
Mass Flowrate=90.79620361 Standard Density=943.7202148 Gross Volume Flowrate=97.66551971 Pressure UN=2.64917016
Temperature UN=1.523870349 Density UN=0.366310716 Standard Density UN=0.371757686 Mass Flow UN=0.091520056
Gross Volume UN=0.37757048 Standard Volume UN=0.385348201 Nett Volume UN=0.988494396 Un CPL=0.000347 Un CTL=0.000697 _ERROR_=1
_N_=12
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=42.98647308 Standard Volume Flowrate=199.2412415 Density=907.8903198 Pressure=9.761479378 Temperature=37.31345749
Mass Flowrate=183.5062866 Standard Density=921.1968384 Gross Volume Flowrate=202.1384125 Pressure UN=2.5617764
Temperature UN=1.523868322 Density UN=0.371639371 Standard Density UN=0.37736702 Mass Flow UN=0.093693674
Gross Volume UN=0.383449197 Standard Volume UN=0.39127785 Nett Volume UN=0.992803395 Un CPL=0.000347 Un CTL=0.000677 _ERROR_=1
_N_=13
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=72.69142914 Standard Volume Flowrate=104.6421127 Density=954.3826294 Pressure=9.848380089 Temperature=37.31354904
Mass Flowrate=101.0683517 Standard Density=967.1860962 Gross Volume Flowrate=106.1940079 Pressure UN=2.539175034
Temperature UN=1.523866177 Density UN=0.370234966 Standard Density UN=0.376963198 Mass Flow UN=0.094315551
Gross Volume UN=0.383601725 Standard Volume UN=0.391075999 Nett Volume UN=0.992199004 Un CPL=0.000347 Un CTL=0.000678 _ERROR_=1
_N_=14
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=72.92094421 Standard Volume Flowrate=104.6160049 Density=954.8782959 Pressure=8.904790878 Temperature=37.30304718
Mass Flowrate=101.3604126 Standard Density=966.980835 Gross Volume Flowrate=106.1627274 Pressure UN=2.811912537
Temperature UN=1.524104238 Density UN=0.364617407 Standard Density UN=0.361596584 Mass Flow UN=0.087721013
Gross Volume UN=0.375026524 Standard Volume UN=0.382662028 Nett Volume UN=0.98682344 Un CPL=0.000347 Un CTL=0.000676 _ERROR_=1
_N_=15
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=74.53404236 Standard Volume Flowrate=58.29196167 Density=957.7504883 Pressure=9.008775711 Temperature=37.2791481
Mass Flowrate=56.57862091 Standard Density=968.8345337 Gross Volume Flowrate=59.14967346 Pressure UN=2.77939868
Temperature UN=1.524646044 Density UN=0.363213032 Standard Density UN=0.360520989 Mass Flow UN=0.088433735
Gross Volume UN=0.373831362 Standard Volume UN=0.381470561 Nett Volume UN=0.985843778 Un CPL=0.000347 Un CTL=0.000672 _ERROR_=1
_N_=16
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=81.73234558 Standard Volume Flowrate=51.26050186 Density=968.2427368 Pressure=9.013922691 Temperature=37.25524902
Mass Flowrate=50.35907745 Standard Density=971.0817871 Gross Volume Flowrate=52.00907135 Pressure UN=2.778722763
Temperature UN=1.52518785 Density UN=0.361808628 Standard Density UN=0.356645793 Mass Flow UN=0.08847414 Gross Volume UN=0.372475922
Standard Volume UN=0.380072802 Nett Volume UN=0.984786928 Un CPL=0.000347 Un CTL=0.000695 _ERROR_=1 _N_=17
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=45.25241852 Standard Volume Flowrate=182.7103729 Density=912.2452393 Pressure=8.625265121 Temperature=37.23134995
Mass Flowrate=168.961441 Standard Density=982.414856 Gross Volume Flowrate=185.3195343 Pressure UN=2.899157763
Temperature UN=1.525729656 Density UN=0.360404253 Standard Density UN=0.378374189 Mass Flow UN=0.085804008
Gross Volume UN=0.370477468 Standard Volume UN=0.378529757 Nett Volume UN=0.98367697 Un CPL=0.000347 Un CTL=0.000675 _ERROR_=1
_N_=18
NOTE: Invalid argument to function INPUT at line 84 column 8.
Date= BSW=76.83015442 Standard Volume Flowrate=99.27960968 Density=958.0273438 Pressure=8.426384926 Temperature=36.50892258
Mass Flowrate=96.5173111 Standard Density=924.2614136 Gross Volume Flowrate=100.7433243 Pressure UN=2.970159054
Temperature UN=1.542510748 Density UN=0.358999848 Standard Density UN=0.360377312 Mass Flow UN=0.084488034
Gross Volume UN=0.368809849 Standard Volume UN=0.376544982 Nett Volume UN=0.982400894 Un CPL=0.000347 Un CTL=0.000675 _ERROR_=1
_N_=19
NOTE: Invalid argument to function INPUT at line 84 column 8.
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
Date= BSW=74.77752686 Standard Volume Flowrate=72.20717621 Density=957.5704346 Pressure=8.1889925 Temperature=36.14315796
Mass Flowrate=70.1020813 Standard Density=972.2071533 Gross Volume Flowrate=73.2494278 Pressure UN=3.054751396
Temperature UN=1.550594807 Density UN=0.357595474 Standard Density UN=0.360527754 Mass Flow UN=0.082916416
Gross Volume UN=0.367083549 Standard Volume UN=0.374854833 Nett Volume UN=0.981241584 Un CPL=0.000347 Un CTL=0.000696 _ERROR_=1
_N_=20
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to 
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      4518 at 84:8   
NOTE: There were 4518 observations read from the data set TEST.DATAUNCERTAINTYTEST.
NOTE: The data set WORK.DATAUNCERTAINTYTESTNEW has 4518 observations and 19 variables.
NOTE: DATA statement used (Total process time):
      real time           0.21 seconds
      cpu time            0.02 seconds
      
86   
87   %studio_hide_wrapper;

my date column.

Thank you for your response!!!

Re: Import CSV to CAS via the web interface does not detect dates

Tom — Fri, 08 May 2020 02:12:30 GMT

It looks like from the values in the photograph at the end of your post don't have date strings at all. Do you want to make DATE values (number of days) from those strings or DATETIME values (number of seconds)?

Either way you cannot convert the existing character variable DATE into a numeric variable that could be used to store either DATE or DATETIME values. You need to make a NEW variable (you can always rename them if you really like the name, but I recommend to not name the variable DATE if you are going to populate it with DATETIME values).

It is hard to tell from those examples how to interpret the beginning of those strings as representing dates. Are they in DMY order? MDY order? YMD order? Is that first value to first of june in 2020? Or perhaps first of june in 1920? Or is it the January sixth? Or perhaps it is 20JUN2001? Or 20JUN1901?

Make sure to use an informat that appropriate for the values you have.

Make sure to attach a format to your new date or datetime values so they display in a human readable way.

data datauncertaintytestnew;
  set test.datauncertaintytest(rename=(date=date_string));
  date = input(scan(date_string,1,' '),mmddyy10.);
  datetime = input(date_string,anydtdtm20.);
  format date date9. datetime datetime20.;
run;

Re: Import CSV to CAS via the web interface does not detect dates

Aiman — Fri, 08 May 2020 03:23:43 GMT

Thank you Tom!! It was Month Date Year format , (Jan16 2020). By the way, I managed to convert it to datetime and date format by running your code. I'm new in SAS Programming to be honest, I didn't know that I cannot convert the existing character variable with the same name, but I learnt it from you. Thank you for that.
Do you mind to explain a bit your line code "scan(date_string,1,' ')", what "1" representing for? the rest of the code I could understand it.

Thank you!!