topic Re:Date format for the Date of Interview variable. in SAS Data Science

Re:Date format for the Date of Interview variable.

anilgvdbm — Wed, 25 Sep 2013 03:54:21 GMT

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

Re:Date format for the Date of Interview variable.

Scott_Mitchell — Wed, 25 Sep 2013 03:58:18 GMT

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

Re:Date format for the Date of Interview variable.

anilgvdbm — Wed, 25 Sep 2013 04:35:12 GMT

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.

Re:Date format for the Date of Interview variable.

Scott_Mitchell — Wed, 25 Sep 2013 04:58:10 GMT

Is it importing as character?

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

Re:Date format for the Date of Interview variable.

anilgvdbm — Wed, 25 Sep 2013 05:37:46 GMT

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

Re:Date format for the Date of Interview variable.

Scott_Mitchell — Wed, 25 Sep 2013 05:58:57 GMT

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;

Re:Date format for the Date of Interview variable.

anilgvdbm — Wed, 25 Sep 2013 07:40:12 GMT

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;

Re:Date format for the Date of Interview variable.

Scott_Mitchell — Wed, 25 Sep 2013 07:58:48 GMT

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

Please provide the data as a csv.

Re:Date format for the Date of Interview variable.

Scott_Mitchell — Wed, 25 Sep 2013 08:03:44 GMT

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;