Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Re:Date format for the Date of Interview variable.

Reply
Contributor
Posts: 59

Re:Date format for the Date of Interview variable.

Dear All i have small problem in the date format ... i have an variable called date of interview from 20/03/1977 to 01/02/2013

so the raw data i had imported into sas in the date of inteview variable its showing some are dates and some are in sas number.

Example is here:

Date_of_interview

20/03/1977

16/05/1977

28370

14/01/1977

28370

28465

28185

16/05/1977

14/01/1977

28370

Please help me in this.

Thank you

Super Contributor
Posts: 297

Re:Date format for the Date of Interview variable.

Posted in reply to anilgvdbm

Anil,

Does you example data above represent the format of the data in the external file or the data once imported to a SAS dataset?

Regards,

Scott

Contributor
Posts: 59

Re:Date format for the Date of Interview variable.

Posted in reply to Scott_Mitchell

Dear Scott,

it's in sas dataset.

in external csv file it's showing date format but when i imported into sas it's showing like above i said.

Super Contributor
Posts: 297

Re:Date format for the Date of Interview variable.

Posted in reply to anilgvdbm

Is it importing as character?

Can you please attach the csv or a subset that you know causes this issue?

Contributor
Posts: 59

Re:Date format for the Date of Interview variable.

Posted in reply to anilgvdbm

Dear Scott,

I am not able to attach the csv file it's contain 5 lakh observation so i pasted some sample data here

Vdsid_hhidYearLandholding GroupDate of InterviewCategory of items item_NameUnit of itemQuantity Total value
INAPAUR1010000001976Labour20/03/1977Non-foodGrinding_milling_exp 2.5
INAPAUR1010000001976Labour16/05/1977Non-foodGrinding_milling_exp 0.4
INAPAUR1010000001976Labour9/2/1977FoodSorghumKilogram1512
INAPAUR1010000001976Labour14/01/1977FoodSorghumKilogram5050
INAPAUR1010000001976Labour9/2/1977FoodRiceKilogram5045
INAPAUR1010000001976Labour14/01/1977FoodRiceKilogram5050
INAPAUR1010000001976Labour18/07/1976FoodRiceKilogram 45
INAPAUR1010000001976Labour18/07/1976FoodRiceKilogram85.6
INAPAUR1010000001976Labour12/6/1977FoodRiceKilogram129.6
INAPAUR1010000001976Labour3/1/1977FoodRiceKilogram1820
INAPAUR1010000001976Labour16/05/1977FoodRiceKilogram2016
INAPAUR1010000001976Labour14/01/1977FoodRiceKilogram2525
INAPAUR1010000001976Labour9/2/1977FoodRiceKilogram2525
INAPAUR1010000001976Labour12/6/1977FoodRiceKilogram2621
INAPAUR1010000001976Labour2/11/1976FoodRiceKilogram3021
INAPAUR1010000001976Labour23/09/1976FoodRiceKilogram3222.4
INAPAUR1010000001976Labour13/10/1976FoodRiceKilogram3222.4
INAPAUR1010000001976Labour16/05/1977FoodRiceKilogram3628.8
INAPAUR1010000001976Labour2/11/1976FoodRiceKilogram4833.6
INAPAUR1010000001976Labour4/9/1976FoodRiceKilogram5236.4
INAPAUR1010000001976Labour4/9/1976FoodRiceKilogram6042
INAPAUR1010000001976Labour12/6/1977FoodRiceKilogram6050
INAPAUR1010000001976Labour15/12/1976FoodRiceKilogram7653.2
INAPAUR1010000001976Labour15/12/1976FoodRiceKilogram8056
INAPAUR1010000001976Labour20/03/1977FoodRiceKilogram8080
INAPAUR1010000001976Labour18/07/1976FoodRiceKilogram160125
INAPAUR1010000001976Labour13/10/1976FoodMeat_goat_chicken_sheepKilogram16
INAPAUR1010000001976Labour3/1/1977FoodMeat_goat_chicken_sheepKilogram25
INAPAUR1010000001976Labour3/1/1977FoodRedgram dhalKilogram0.251.25
INAPAUR1010000001976Labour23/09/1976FoodOnionKilogram10.5
INAPAUR1010000001976Labour13/10/1976FoodOnionKilogram10.5
INAPAUR1010000001976Labour15/12/1976FoodOnionKilogram1.51.35
INAPAUR1010000001976Labour4/9/1976FoodOnionKilogram20.8
INAPAUR1010000001976Labour2/11/1976FoodOnionKilogram21
INAPAUR1010000001976Labour12/6/1977FoodChillies 5.2
INAPAUR1010000001976Labour18/07/1976FoodChillies 7.4
INAPAUR1010000001976Labour16/05/1977FoodChillies 11.4
INAPAUR1010000001976Labour9/2/1977FoodChilliesKilogram0.51.3
INAPAUR1010000001976Labour9/2/1977FoodChilliesKilogram12.5
INAPAUR1010000001976Labour20/03/1977FoodChilliesKilogram16
INAPAUR1010000001976Labour14/01/1977FoodChilliesKilogram1.54
INAPAUR1010000001976Labour23/09/1976FoodChilliesKilogram33.6
INAPAUR1010000001976Labour13/10/1976FoodChilliesKilogram33.6
INAPAUR1010000001976Labour2/11/1976FoodChilliesKilogram33.6
INAPAUR1010000001976Labour4/9/1976FoodChilliesKilogram34.7
INAPAUR1010000001976Labour15/12/1976FoodChilliesKilogram56
INAPAUR1010000001976Labour3/1/1977FoodBrinjalKilogram33
INAPAUR1010000001976Labour3/1/1977FoodOther vegetablesKilogram22
INAPAUR1010000001976Labour20/03/1977FoodOther_Spices 0.5
INAPAUR1010000001976Labour14/01/1977FoodOther_Spices 1
INAPAUR1010000001976Labour10/2/1977FoodOther_Spices 1.3
INAPAUR1010000001976Labour9/2/1977FoodOther_Spices 1.3
INAPAUR1010000001976Labour12/6/1977FoodOther_Spices 1.3
INAPAUR1010000001976Labour16/05/1977FoodOther_Spices 1.7
INAPAUR1010000001976Labour18/07/1976FoodOther_Spices 1.85
INAPAUR1010000001976Labour23/09/1976FoodOther_SpicesKilogram0.250.75
INAPAUR1010000001976Labour15/12/1976FoodOther_SpicesKilogram0.51.4
INAPAUR1010000001976Labour13/10/1976FoodOther_SpicesKilogram0.51.5
INAPAUR1010000001976Labour2/11/1976FoodOther_SpicesKilogram0.51.5
INAPAUR1010000001976Labour4/9/1976FoodOther_SpicesKilogram10.2
INAPAUR1010000001976Labour4/9/1976FoodOther_SpicesKilogram13
INAPAUR1010000001976Labour23/09/1976FoodOther_SpicesKilogram1.50.3
INAPAUR1010000001976Labour13/10/1976FoodOther_SpicesKilogram20.4
INAPAUR1010000001976Labour20/03/1977FoodOther_SpicesKilogram31
INAPAUR1010000001976Labour2/11/1976FoodOther_SpicesKilogram40.8
INAPAUR1010000001976Labour9/2/1977FoodOther_SpicesKilogram45
INAPAUR1010000001976Labour15/12/1976FoodOther_SpicesKilogram81.6
INAPAUR1010000001976Labour14/01/1977FoodGur_jaggery 0.5
INAPAUR1010000001976Labour16/05/1977Non-foodAlcoholic beverages 5
INAPAUR1010000001976Labour18/07/1976Non-foodAlcoholic beverages 9.25
INAPAUR1010000001976Labour20/03/1977Non-foodAlcoholic beverages 18
INAPAUR1010000001976Labour4/9/1976Non-foodAlcoholic beverages 11.8
INAPAUR1010000001976Labour9/2/1977Non-foodAlcoholic beveragesLitres51
INAPAUR1010000001976Labour14/01/1977Non-foodAlcoholic beveragesLitres102
INAPAUR1010000001976Labour16/05/1977Non-foodTobacco, beedi 1.71
INAPAUR1010000001976Labour14/01/1977Non-foodTobacco, beedi 3.3
INAPAUR1010000001976Labour23/09/1976Non-foodTobacco, beedi 4
INAPAUR1010000001976Labour13/10/1976Non-foodTobacco, beedi 4
INAPAUR1010000001976Labour2/11/1976Non-foodTobacco, beedi 4
INAPAUR1010000001976Labour9/2/1977Non-foodTobacco, beedi 5
INAPAUR1010000001976Labour18/07/1976Non-foodTobacco, beedi 5.55
INAPAUR1010000001976Labour12/6/1977Non-foodTobacco, beedi 7.8
INAPAUR1010000001976Labour15/12/1976Non-foodTobacco, beedi 8.6
INAPAUR1010000001976Labour16/05/1977Non-foodTobacco, beedi 17.1
INAPAUR1010000001976Labour4/9/1976Non-foodTobacco, beediKilogram0.754.5
INAPAUR1010000001976Labour20/03/1977Non-foodTobacco, beediNumbers4005
INAPAUR1010000001976Labour18/07/1976Non-foodClothing 10
INAPAUR1010000001976Labour4/9/1976Non-foodClothing 16
INAPAUR1010000001976Labour12/6/1977Non-foodClothing 20
INAPAUR1010000001976Labour13/10/1976Non-foodClothing 25
INAPAUR1010000001976Labour16/05/1977Non-foodClothing 30
INAPAUR1010000001976Labour13/10/1976Non-foodClothing 30
INAPAUR1010000001976Labour9/2/1977Non-foodMedical_exp 3
INAPAUR1010000001976Labour20/03/1977Non-foodMedical_exp 5
INAPAUR1010000001976Labour15/12/1976Non-foodMedical_exp 11
INAPAUR1010000001976Labour4/9/1976Non-foodTravel and Entertainment 1.4
INAPAUR1010000001976Labour9/2/1977Non-foodTravel and Entertainment 2
INAPAUR1010000001976Labour4/9/1976Non-foodElectricity_Water_exp 0.6
INAPAUR1010000001976Labour14/01/1977Non-foodElectricity_Water_exp 1
INAPAUR1010000001976Labour12/6/1977Non-foodElectricity_Water_exp 2.6
INAPAUR1010000001976Labour9/2/1977Non-foodElectricity_Water_expKilogram500.5
INAPAUR1010000001976Labour14/01/1977Non-foodElectricity_Water_expKilogram2002
INAPAUR1010000001976Labour18/07/1976Non-foodOthers 1.85
INAPAUR1010000001976Labour9/2/1977Non-foodOthersLitres11.6
INAPAUR1010000001976Labour23/09/1976Non-foodOthersLitres1.52.25
INAPAUR1010000001976Labour13/10/1976Non-foodOthersLitres1.52.25
INAPAUR1010000001976Labour2/11/1976Non-foodOthersLitres1.52.25
INAPAUR1010000001976Labour20/03/1977Non-foodOthersLitres33.6
INAPAUR1010000001976Labour15/12/1976Non-foodOthersLitres34.5
INAPAUR1010000001976Labour23/09/1976Non-foodOthersNumbers40.4
Super Contributor
Posts: 297

Re:Date format for the Date of Interview variable.

Posted in reply to anilgvdbm

This appears to work.

DATA WORK.TEST50_TXT;

    LENGTH

        VDSID_HHID       $ 16

        YEAR               8

        'LANDHOLDING GROUP'N $ 6

        'DATE OF INTERVIEW'N   8

        'CATEGORY OF ITEMS'N $ 8

        ITEM_NAME        $ 24

        'UNIT OF ITEM'N  $ 8

        QUANTITY           8

        'TOTAL VALUE'N     8 ;

    FORMAT

        VDSID_HHID       $CHAR16.

        YEAR             BEST4.

        'LANDHOLDING GROUP'N $CHAR6.

        'DATE OF INTERVIEW'N DDMMYY10.

        'CATEGORY OF ITEMS'N $CHAR8.

        ITEM_NAME        $CHAR24.

        'UNIT OF ITEM'N  $CHAR8.

        QUANTITY         BEST4.

        'TOTAL VALUE'N   BEST4. ;

    INFORMAT

        VDSID_HHID       $CHAR16.

        YEAR             BEST4.

        'LANDHOLDING GROUP'N $CHAR6.

        'DATE OF INTERVIEW'N DDMMYY10.

        'CATEGORY OF ITEMS'N $CHAR8.

        ITEM_NAME        $CHAR24.

        'UNIT OF ITEM'N  $CHAR8.

        QUANTITY         BEST4.

        'TOTAL VALUE'N   BEST4. ;

    INFILE 'E:\TEST50.TXT'

        LRECL=85

  FIRSTOBS=2

        DLM='09'X

        MISSOVER

        DSD ;

    INPUT

        VDSID_HHID       : $CHAR16.

        YEAR             : ?? BEST4.

        'LANDHOLDING GROUP'N : $CHAR6.

        'DATE OF INTERVIEW'N : ?? DDMMYY10.

        'CATEGORY OF ITEMS'N : $CHAR8.

        ITEM_NAME        : $CHAR24.

        'UNIT OF ITEM'N  : $CHAR8.

        QUANTITY         : ?? COMMA4.

        'TOTAL VALUE'N   : ?? COMMA4. ;

RUN;

Contributor
Posts: 59

Re:Date format for the Date of Interview variable.

Posted in reply to Scott_Mitchell

It's not working because all my data is in csv format it's huge data i am not able to do it like this for all huge files can we apply any data steps after importing to sas?

Here is the example which i tried:

proc import datafile="E:\VLS consumption data\New data\all76-83.xlsx"

out=vls7683

dbms=xlsx ;

run;

data vls7683;

set vls7683;

date_int=input(Date_of_Interview,10.);

format date_int  ddmmyy10.;

run;

Super Contributor
Posts: 297

Re:Date format for the Date of Interview variable.

Posted in reply to anilgvdbm

Then why would you send me a sample that is TAB delimited, when your file is CSV???

Please provide the data as a csv.

Super Contributor
Posts: 297

Re:Date format for the Date of Interview variable.

Posted in reply to Scott_Mitchell

I converted your sample to CSV and imported it successfully using:

PROC IMPORT DATAFILE="E:\TEST50.CSV"

     OUT=WANT

     DBMS=CSV

     REPLACE;

     GETNAMES=YES;

RUN;

Ask a Question
Discussion stats
  • 8 replies
  • 385 views
  • 0 likes
  • 2 in conversation