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: 57

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.

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: 57

Re:Date format for the Date of Interview variable.

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.

Is it importing as character?

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

Contributor
Posts: 57

Re:Date format for the Date of Interview variable.

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.

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: 57

Re:Date format for the Date of Interview variable.

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.

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.

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
  • 383 views
  • 0 likes
  • 2 in conversation