Re:Date format for the Date of Interview variable.

[anilgvdbm]

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

[Scott_Mitchell]

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

[anilgvdbm]

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.

[Scott_Mitchell]

Is it importing as character?

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

[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_hhid	Year	Landholding Group	Date of Interview	Category of items	item_Name	Unit of item	Quantity	Total value
INAPAUR101000000	1976	Labour	20/03/1977	Non-food	Grinding_milling_exp			2.5
INAPAUR101000000	1976	Labour	16/05/1977	Non-food	Grinding_milling_exp			0.4
INAPAUR101000000	1976	Labour	9/2/1977	Food	Sorghum	Kilogram	15	12
INAPAUR101000000	1976	Labour	14/01/1977	Food	Sorghum	Kilogram	50	50
INAPAUR101000000	1976	Labour	9/2/1977	Food	Rice	Kilogram	50	45
INAPAUR101000000	1976	Labour	14/01/1977	Food	Rice	Kilogram	50	50
INAPAUR101000000	1976	Labour	18/07/1976	Food	Rice	Kilogram		45
INAPAUR101000000	1976	Labour	18/07/1976	Food	Rice	Kilogram	8	5.6
INAPAUR101000000	1976	Labour	12/6/1977	Food	Rice	Kilogram	12	9.6
INAPAUR101000000	1976	Labour	3/1/1977	Food	Rice	Kilogram	18	20
INAPAUR101000000	1976	Labour	16/05/1977	Food	Rice	Kilogram	20	16
INAPAUR101000000	1976	Labour	14/01/1977	Food	Rice	Kilogram	25	25
INAPAUR101000000	1976	Labour	9/2/1977	Food	Rice	Kilogram	25	25
INAPAUR101000000	1976	Labour	12/6/1977	Food	Rice	Kilogram	26	21
INAPAUR101000000	1976	Labour	2/11/1976	Food	Rice	Kilogram	30	21
INAPAUR101000000	1976	Labour	23/09/1976	Food	Rice	Kilogram	32	22.4
INAPAUR101000000	1976	Labour	13/10/1976	Food	Rice	Kilogram	32	22.4
INAPAUR101000000	1976	Labour	16/05/1977	Food	Rice	Kilogram	36	28.8
INAPAUR101000000	1976	Labour	2/11/1976	Food	Rice	Kilogram	48	33.6
INAPAUR101000000	1976	Labour	4/9/1976	Food	Rice	Kilogram	52	36.4
INAPAUR101000000	1976	Labour	4/9/1976	Food	Rice	Kilogram	60	42
INAPAUR101000000	1976	Labour	12/6/1977	Food	Rice	Kilogram	60	50
INAPAUR101000000	1976	Labour	15/12/1976	Food	Rice	Kilogram	76	53.2
INAPAUR101000000	1976	Labour	15/12/1976	Food	Rice	Kilogram	80	56
INAPAUR101000000	1976	Labour	20/03/1977	Food	Rice	Kilogram	80	80
INAPAUR101000000	1976	Labour	18/07/1976	Food	Rice	Kilogram	160	125
INAPAUR101000000	1976	Labour	13/10/1976	Food	Meat_goat_chicken_sheep	Kilogram	1	6
INAPAUR101000000	1976	Labour	3/1/1977	Food	Meat_goat_chicken_sheep	Kilogram	2	5
INAPAUR101000000	1976	Labour	3/1/1977	Food	Redgram dhal	Kilogram	0.25	1.25
INAPAUR101000000	1976	Labour	23/09/1976	Food	Onion	Kilogram	1	0.5
INAPAUR101000000	1976	Labour	13/10/1976	Food	Onion	Kilogram	1	0.5
INAPAUR101000000	1976	Labour	15/12/1976	Food	Onion	Kilogram	1.5	1.35
INAPAUR101000000	1976	Labour	4/9/1976	Food	Onion	Kilogram	2	0.8
INAPAUR101000000	1976	Labour	2/11/1976	Food	Onion	Kilogram	2	1
INAPAUR101000000	1976	Labour	12/6/1977	Food	Chillies			5.2
INAPAUR101000000	1976	Labour	18/07/1976	Food	Chillies			7.4
INAPAUR101000000	1976	Labour	16/05/1977	Food	Chillies			11.4
INAPAUR101000000	1976	Labour	9/2/1977	Food	Chillies	Kilogram	0.5	1.3
INAPAUR101000000	1976	Labour	9/2/1977	Food	Chillies	Kilogram	1	2.5
INAPAUR101000000	1976	Labour	20/03/1977	Food	Chillies	Kilogram	1	6
INAPAUR101000000	1976	Labour	14/01/1977	Food	Chillies	Kilogram	1.5	4
INAPAUR101000000	1976	Labour	23/09/1976	Food	Chillies	Kilogram	3	3.6
INAPAUR101000000	1976	Labour	13/10/1976	Food	Chillies	Kilogram	3	3.6
INAPAUR101000000	1976	Labour	2/11/1976	Food	Chillies	Kilogram	3	3.6
INAPAUR101000000	1976	Labour	4/9/1976	Food	Chillies	Kilogram	3	4.7
INAPAUR101000000	1976	Labour	15/12/1976	Food	Chillies	Kilogram	5	6
INAPAUR101000000	1976	Labour	3/1/1977	Food	Brinjal	Kilogram	3	3
INAPAUR101000000	1976	Labour	3/1/1977	Food	Other vegetables	Kilogram	2	2
INAPAUR101000000	1976	Labour	20/03/1977	Food	Other_Spices			0.5
INAPAUR101000000	1976	Labour	14/01/1977	Food	Other_Spices			1
INAPAUR101000000	1976	Labour	10/2/1977	Food	Other_Spices			1.3
INAPAUR101000000	1976	Labour	9/2/1977	Food	Other_Spices			1.3
INAPAUR101000000	1976	Labour	12/6/1977	Food	Other_Spices			1.3
INAPAUR101000000	1976	Labour	16/05/1977	Food	Other_Spices			1.7
INAPAUR101000000	1976	Labour	18/07/1976	Food	Other_Spices			1.85
INAPAUR101000000	1976	Labour	23/09/1976	Food	Other_Spices	Kilogram	0.25	0.75
INAPAUR101000000	1976	Labour	15/12/1976	Food	Other_Spices	Kilogram	0.5	1.4
INAPAUR101000000	1976	Labour	13/10/1976	Food	Other_Spices	Kilogram	0.5	1.5
INAPAUR101000000	1976	Labour	2/11/1976	Food	Other_Spices	Kilogram	0.5	1.5
INAPAUR101000000	1976	Labour	4/9/1976	Food	Other_Spices	Kilogram	1	0.2
INAPAUR101000000	1976	Labour	4/9/1976	Food	Other_Spices	Kilogram	1	3
INAPAUR101000000	1976	Labour	23/09/1976	Food	Other_Spices	Kilogram	1.5	0.3
INAPAUR101000000	1976	Labour	13/10/1976	Food	Other_Spices	Kilogram	2	0.4
INAPAUR101000000	1976	Labour	20/03/1977	Food	Other_Spices	Kilogram	3	1
INAPAUR101000000	1976	Labour	2/11/1976	Food	Other_Spices	Kilogram	4	0.8
INAPAUR101000000	1976	Labour	9/2/1977	Food	Other_Spices	Kilogram	4	5
INAPAUR101000000	1976	Labour	15/12/1976	Food	Other_Spices	Kilogram	8	1.6
INAPAUR101000000	1976	Labour	14/01/1977	Food	Gur_jaggery			0.5
INAPAUR101000000	1976	Labour	16/05/1977	Non-food	Alcoholic beverages			5
INAPAUR101000000	1976	Labour	18/07/1976	Non-food	Alcoholic beverages			9.25
INAPAUR101000000	1976	Labour	20/03/1977	Non-food	Alcoholic beverages			18
INAPAUR101000000	1976	Labour	4/9/1976	Non-food	Alcoholic beverages			11.8
INAPAUR101000000	1976	Labour	9/2/1977	Non-food	Alcoholic beverages	Litres	5	1
INAPAUR101000000	1976	Labour	14/01/1977	Non-food	Alcoholic beverages	Litres	10	2
INAPAUR101000000	1976	Labour	16/05/1977	Non-food	Tobacco, beedi			1.71
INAPAUR101000000	1976	Labour	14/01/1977	Non-food	Tobacco, beedi			3.3
INAPAUR101000000	1976	Labour	23/09/1976	Non-food	Tobacco, beedi			4
INAPAUR101000000	1976	Labour	13/10/1976	Non-food	Tobacco, beedi			4
INAPAUR101000000	1976	Labour	2/11/1976	Non-food	Tobacco, beedi			4
INAPAUR101000000	1976	Labour	9/2/1977	Non-food	Tobacco, beedi			5
INAPAUR101000000	1976	Labour	18/07/1976	Non-food	Tobacco, beedi			5.55
INAPAUR101000000	1976	Labour	12/6/1977	Non-food	Tobacco, beedi			7.8
INAPAUR101000000	1976	Labour	15/12/1976	Non-food	Tobacco, beedi			8.6
INAPAUR101000000	1976	Labour	16/05/1977	Non-food	Tobacco, beedi			17.1
INAPAUR101000000	1976	Labour	4/9/1976	Non-food	Tobacco, beedi	Kilogram	0.75	4.5
INAPAUR101000000	1976	Labour	20/03/1977	Non-food	Tobacco, beedi	Numbers	400	5
INAPAUR101000000	1976	Labour	18/07/1976	Non-food	Clothing			10
INAPAUR101000000	1976	Labour	4/9/1976	Non-food	Clothing			16
INAPAUR101000000	1976	Labour	12/6/1977	Non-food	Clothing			20
INAPAUR101000000	1976	Labour	13/10/1976	Non-food	Clothing			25
INAPAUR101000000	1976	Labour	16/05/1977	Non-food	Clothing			30
INAPAUR101000000	1976	Labour	13/10/1976	Non-food	Clothing			30
INAPAUR101000000	1976	Labour	9/2/1977	Non-food	Medical_exp			3
INAPAUR101000000	1976	Labour	20/03/1977	Non-food	Medical_exp			5
INAPAUR101000000	1976	Labour	15/12/1976	Non-food	Medical_exp			11
INAPAUR101000000	1976	Labour	4/9/1976	Non-food	Travel and Entertainment			1.4
INAPAUR101000000	1976	Labour	9/2/1977	Non-food	Travel and Entertainment			2
INAPAUR101000000	1976	Labour	4/9/1976	Non-food	Electricity_Water_exp			0.6
INAPAUR101000000	1976	Labour	14/01/1977	Non-food	Electricity_Water_exp			1
INAPAUR101000000	1976	Labour	12/6/1977	Non-food	Electricity_Water_exp			2.6
INAPAUR101000000	1976	Labour	9/2/1977	Non-food	Electricity_Water_exp	Kilogram	50	0.5
INAPAUR101000000	1976	Labour	14/01/1977	Non-food	Electricity_Water_exp	Kilogram	200	2
INAPAUR101000000	1976	Labour	18/07/1976	Non-food	Others			1.85
INAPAUR101000000	1976	Labour	9/2/1977	Non-food	Others	Litres	1	1.6
INAPAUR101000000	1976	Labour	23/09/1976	Non-food	Others	Litres	1.5	2.25
INAPAUR101000000	1976	Labour	13/10/1976	Non-food	Others	Litres	1.5	2.25
INAPAUR101000000	1976	Labour	2/11/1976	Non-food	Others	Litres	1.5	2.25
INAPAUR101000000	1976	Labour	20/03/1977	Non-food	Others	Litres	3	3.6
INAPAUR101000000	1976	Labour	15/12/1976	Non-food	Others	Litres	3	4.5
INAPAUR101000000	1976	Labour	23/09/1976	Non-food	Others	Numbers	4	0.4

[Scott_Mitchell]

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;

[anilgvdbm]

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;

[Scott_Mitchell]

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

Please provide the data as a csv.

[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;