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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.