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
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
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.
Is it importing as character?
Can you please attach the csv or a subset that you know causes this issue?
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 |
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;
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;
Then why would you send me a sample that is TAB delimited, when your file is CSV???
Please provide the data as a csv.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.