About fengyuwuzu

fengyuwuzu · ‎02-22-2016

Thank you very much for your prompt replies, and your suggestions. Yes, it is a good idea to use a small portion of the data set to test. when you said: "You want a full join if you only want IDs with orders." -- it should be inner join, not full join, right?

fengyuwuzu · ‎02-22-2016

data set is very large. I ran the first step, and there was no error.The new table has many many more rows than the original table A. I guess this is due to some IDs have multiple orders. I want to make sure I did it correctly before moving forward.

fengyuwuzu · ‎02-22-2016

In table A, I have ID, gender, age, etc demographics info; In table B, I have ID, order_datetime, order_amount etc order details info. Table A has more IDs than table B (many IDs never put an order), and in table B each ID may have multiple orders. Now I want to merge them, to have a new table, which has all info from Table A, and also order_datetime from Table B. In this case, shall I use the left join in proc sql? proc sql; create table demo_order as select a.ID, a.gender, a.age, b.order_date from demographics as a left join order_details as b on a.ID eq b.ID; quit; Then, I will calculate how many orders one ID made per day. Can I use the following code? proc sql; create table order_per_day as select ID, order_date, count(*) as num_orders from demo_order group by ID, order_date; quit; I also want to find out which IDs never put an order, and count how many. Can I count directly in the proc sql step? proc sql; create table never_ordered as select ID, age, gender from demo_order where order_date is missing; quit;

fengyuwuzu · ‎02-19-2016

Yes, andreas_lds! This solved the problem. Now I have learned this which will be helpful in future.

fengyuwuzu · ‎02-19-2016

Thank you, smijosimon. The same thing, still blank.

fengyuwuzu · ‎02-19-2016

right now, the solution I can find is, first read the time in as characters, and then use the following data new; set LOGIN; new_login = input(LOGIN_TIMESTAMP, anydtdtm.); format new_login datetime.; run; to convert to datetime format. It works. Not sure the reason why I cannot import as datetime format directly.

fengyuwuzu · ‎02-19-2016

if I read them as characters, I got this (a screen cut of first 5 lines):

fengyuwuzu · ‎02-19-2016

I printed out the first 10 rows of the data&colon; 54 data _NULL_; 55 infile "D:\data\LOGIN.csv" obs=10; 56 input; 57 put _infile_; 58 run; NOTE: The infile "D:\data\LOGIN.csv" is: Filename=D:\data\LOGIN.csv, RECFM=V,LRECL=32767, File Size (bytes)=129790621, Last Modified=20Nov2034:08:54:54, Create Time=09Dec2015:12:16:28 MEMBER_ID,LOGIN_TIMESTAMP,LOGOUT_TIMESTAMP "101819860625","2014-01-01-10.11.47.518000","2014-01-01-11.12.11.594000" "111619621641","2014-01-01-10.46.56.362000","2014-01-01-10.50.33.830000" "100319558420","2014-01-01-13.00.56.340000","2014-01-01-13.19.28.438000" "100319558420","2014-01-01-13.04.51.569000","2014-01-01-13.24.16.206000" "100319558420","2014-01-01-13.10.43.651000","2014-01-01-13.29.27.735000" "012719344260","2014-01-01-20.36.43.038000","2014-01-01-21.23.46.843000" "110119839130","2014-01-01-19.31.08.188000","2014-01-01-20.06.23.152000" "110119839130","2014-01-01-20.41.12.057000","2014-01-01-21.36.21.783000" "062119809305","2014-01-01-10.13.22.648000","2014-01-01-10.55.23.402000" NOTE: 10 records were read from the infile "D:\data\LOGIN.csv". The minimum record length was 49. The maximum record length was 92. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds

fengyuwuzu · ‎02-19-2016

I tried anydtdtm40. and anydtdtm26, did not work. LOGIN_TIMESTAMP LOGOUT_TIMESTAMP 2014-01-01-10.11.47.518000 2014-01-01-11.12.11.594000 2014-01-01-10.46.56.362000 2014-01-01-10.50.33.830000 2014-01-01-13.00.56.340000 2014-01-01-13.19.28.438000 2014-01-01-13.04.51.569000 2014-01-01-13.24.16.206000 2014-01-01-13.10.43.651000 2014-01-01-13.29.27.735000 2014-01-01-20.36.43.038000 2014-01-01-21.23.46.843000 2014-01-01-19.31.08.188000 2014-01-01-20.06.23.152000 2014-01-01-20.41.12.057000 2014-01-01-21.36.21.783000 2014-01-01-10.13.22.648000 2014-01-01-10.55.23.402000 2014-01-01-12.07.37.904000 2014-01-01-12.33.14.021000 2014-01-01-15.08.29.764000 2014-01-01-15.47.05.939000 2014-01-01-20.59.14.579000 2014-01-01-21.01.09.404000 2014-01-01-19.22.55.049000 2014-01-01-19.48.15.591000 above is the part of the data, the two columns I have problem. Below is from the log file: NOTE: The infile 'D:\data\LOGIN.csv ' is: Filename=D:\data\LOGIN.csv, RECFM=V,LRECL=32767, File Size (bytes)=129790621, Last Modified=20Nov2034:08:54:54, Create Time=09Dec2015:12:16:28 NOTE: 1410427 records were read from the infile 'D:\data\LOGIN.csv'. The minimum record length was 64. The maximum record length was 92. NOTE: The data set WORK.LOGIN has 1410427 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 1.37 seconds cpu time 0.99 seconds

fengyuwuzu · ‎02-18-2016

Thanks. Right, your code works. I use the following infile code, which does not work. Not sure where the problem is. data WORK.LOGIN ; infile 'D:\data\LOGIN.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ; informat MEMBER_ID $34. ; informat LOGIN_TIMESTAMP ANYDTDTM. ; informat LOGOUT_TIMESTAMP ANYDTDTM. ; format MEMBER_ID $34. ; format LOGIN_TIMESTAMP datetime. ; format LOGOUT_TIMESTAMP datetime. ; input MEMBER_ID $ LOGIN_TIMESTAMP LOGOUT_TIMESTAMP; run;

fengyuwuzu · ‎02-18-2016

to read anydtdte. does not work either. all values are blank after importing

fengyuwuzu · ‎02-18-2016

I used informat anydtdt40. and format datetime. to read them in, but they are not read in at all. all blank.

fengyuwuzu · ‎02-18-2016

each csv file has a header row. when I used the code below, the header row of the first file is skpped, but all header rows from the second file were kept. data mydata; infile "c:\temp\*.csv" dlm=',' missover dsd lrecl=32767 firstobs=2; input var1 var2 $ var3 var4 ... ; how to solve this problem? Thanks!

fengyuwuzu · ‎02-17-2016

Thank you all the replies. So nice to read all your replies. Learned a lot.

fengyuwuzu · ‎02-17-2016

do I need to use modify to remove the password first, or there is a way I can be prompted to input the password when running the sas import code?

Online Status	Offline
Date Last Visited	‎08-14-2024 10:40 PM

calculated estimated survival rate at a certain time point (say, 18 mo...

Stratified analysis in non ITT population

Re: Proc import: how to add back blank values due to the same values a...

Proc import: how to add back blank values due to the same values as pr...

Re: Puzzled: Hazard ratio is 0 when both arms have events

Puzzled: Hazard ratio is 0 when both arms have events

Re: median survival not reached in proc lifetest when there are only 2...

Re: median survival not reached in proc lifetest when there are only 2...

Re: median survival not reached in proc lifetest when there are only 2...

median survival not reached in proc lifetest when there are only 2 pat...

Re: count events in one dataset between two visits in another dataset

Re: count events in one dataset between two visits in another dataset

Re: count events in one dataset between two visits in another dataset

Re: separate a string into two variables

Re: separate a string into two variables

ERROR: Some character data was lost during transcoding

hide the time and date on the rtf generated by ODS

proc sort and dupout: how to get the pairs of duplicates

converting numeric to character by put statement: how to avoid "." for...

Re: LOCF with visit missing in the data set

Re: proc sql: add one column from one table to another table and count

Re: proc sql: add one column from one table to another table and count

proc sql: add one column from one table to another table and count

Re: what time format should I use for values like this: 2014-01-01-10....

Re: what time format should I use for values like this: 2014-01-01-10....

Re: what time format should I use for values like this: 2014-01-01-10....

Re: what time format should I use for values like this: 2014-01-01-10....

Re: what time format should I use for values like this: 2014-01-01-10....

Re: what time format should I use for values like this: 2014-01-01-10....

Re: what time format should I use for values like this: 2014-01-01-10....

Re: what time format should I use for values like this: 2014-01-01-10....

what time format should I use for values like this: 2014-01-01-10.11.4...

import multiple csv files and skip the header rows of all files

Re: Selecting distinct Combinations of two variables

how to import a password protected csv file?