need I use the input function?

[tianerhu]

proc sql;
select 
  distinct 
    qtr(datepart(tran_date)) 
      as quarter, 
    year(datepart(tran_date)) 
      as year
from sales_q1;

quit;

Do I need use the function input( ) to transfer the character variable to numeral variable ?

The variable 'trans_date' is character variable.

Like this : 01JAN17:09:43:00

[PaigeMiller]

Are you sure it isn't numeric with a format? What does PROC CONTENTS say?

[tianerhu]

[PaigeMiller]

Yes, if you want to use DATEPART then it has to be numeric. Example:

year(datepart(input(tran_date,anydtdtm.))) as year

[Reeza]

Yes, but go back and fix it in your data import or an earlier step so you're not doing it throughout your code.

[Tom]

Yes.

To convert strings like that use the DATETIME informat.

The INPUT() function does not care if you use a width that is larger than the length of the string being read. The maximum width that DATETIME informat supports is 40.

input(tran_date,datetime40.)

[tianerhu]

but , the code without using input() works . I import data using proc import , is this the reason?

proc import datafile = "C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\test file.csv"
 out = test
 dbms = CSV
 replace
 ;
run;
proc print data = test;
run;
proc import datafile = "C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\Sales_Q1.csv"
 out = Sales_Q1
 dbms = CSV
 replace
 ;
run;
proc means data=sales_q1;
var price;
run;
proc sql;
select 
  distinct 
    qtr(datepart(tran_date)) 
      as quarter, 
    year(datepart(tran_date)) 
      as year
from sales_q1;
quit;
proc import datafile = "C:\SAS data and program\data\data from web source\Geo-targeting\Data Files/locations.csv"
 out = locations
 dbms = CSV
 replace
 ;
run;

proc import datafile = "C:\SAS data and program\data\data from web source\Geo-targeting\Data Files/postcode.csv"
 out = postcode
 dbms = CSV
 replace
 ;
run;
proc sql;
create table sales_q1_loc as
select a.*, b.os_x as store_x, b.os_y as store_y
from sales_q1 a, locations b
where a.store_postcode = b.postcode;
quit;
proc sql;
create table sales_q1_loc_pos as
select a.*, b.os_x as cust_x, b.os_y as cust_y
from sales_q1_loc a left join postcode b
on a.customer_postcode = b.postcode;
quit;
data sales_check;
set sales_q1_loc_pos;
where store_x = . or
      store_y = . or
      cust_x = . or
      cust_y = .;

keep customer_postcode store_postcode store_x store_y cust_x cust_y;
run;
proc print data = sales_check;
run;
proc contents data = apple.sales_q1;
run;

*******************************************************************************************************************

*******************************************************************************************************************

*******************************************************************************************************************

libname apple 'C:\SAS data and program\data\data from web source\Geo-targeting\Data Files';
data apple.computers;
infile 'C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\computers.csv' dsd firstobs=2;
input configuration screen_size battery_hours ram processor_speed SSD $ HD_size $;
run;
data apple.locations;
infile 'C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\locations.csv' dsd firstobs=2;
input Postcode $ OS_X OS_Y Lat Long ;
run;
data apple.postcode;
infile 'C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\postcode.csv' dsd firstobs=2;
input Postcode $ OS_X OS_Y;
run;
data apple.sales_q1;
infile 'C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\sales_q1.csv' dsd firstobs=2;
length tran_date $ 16;
input tran_date $ Configuration Customer_Postcode $ Store_Postcode $ month price;
run;
proc contents data = apple.sales_q1;
run;
proc sql;
select 
  distinct 
    qtr(datepart(input(tran_date,datetime18.))) 
      as quarter, 
    year(datepart(input(tran_date,datetime18.))) 
      as year
from apple.sales_q1;
quit;

proc sql;
create table sales_q1_loc as
select a.*, b.os_x as store_x, b.os_y as store_y
from apple.sales_q1 a, apple.locations b
where a.store_postcode = b.postcode;
quit;

proc print data = sales_q1_loc(obs=10);
run;
proc sql;
create table sales_q1_loc_pos as
select a.*, b.os_x as cust_x, b.os_y as cust_y
from sales_q1_loc a left join apple.postcode b
on a.customer_postcode = b.postcode;
quit;
data sales_check;
set sales_q1_loc_pos;
where store_x = . or
      store_y = . or
      cust_x = . or
      cust_y = .;

keep customer_postcode store_postcode store_x store_y cust_x cust_y;
run;
proc means data = sales_check;
run;

The last proc (proc means) in the first code , I can detect 9698 missing value .
The last proc (proc means) in the second code , I can detect 0 missing value.

But, I just use different way to read data from raw file:

the first code : proc import

the second code : data step

why ?

[Tom]

In this step you let PROC IMPORT make its best GUESS at how to define TRAN_DATE based on what it sees in just the first few lines of the CSV file.

proc import datafile = "C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\Sales_Q1.csv"
 out = Sales_Q1
 dbms = CSV
 replace
 ;
run;

To see what decision it made check the resulting dataset with PROC CONTENTS.

proc contents data=Sales_Q1;
run;

In this data step you set the variable tran_date  as character with a maximum length of 16 bytes.

data apple.sales_q1;
infile 'C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\sales_q1.csv' dsd firstobs=2;
length tran_date $ 16;
input tran_date $ Configuration Customer_Postcode $ Store_Postcode $ month price;
run;

If you want it to define TRAN_DATE as a DATETIME value then tell it what INFORMAT to use when reading the text from the CSV for that variable. And attach an appropriate format so the values are displayed in a way that humans will recognize.

data apple.sales_q1;
  infile 'C:\SAS data and program\data\data from web source\Geo-targeting\Data Files\sales_q1.csv' dsd firstobs=2;
  input tran_date :datetime. Configuration Customer_Postcode $ Store_Postcode $ month price;
  format tran_date datetime19.;
run;

[tianerhu]

Thank you .