I have a data set of daily prices for each ID that looks like the following example.  data have; input id $1. date :yymmdd10. price 5.; format date yymmdd10.; datalines; A 2000-01-02 3 A 2000-01-03 4 A 2000-01-04 6 A 2000-01-05 4 A 2000-01-06 7 A 2000-01-07 8 A 2000-01-08 9 A 2000-01-09 6 A 2000-01-10 7 A 2000-01-11 5 A 2000-01-12 6 A 2000-01-13 5 B 2000-01-02 4 B 2000-01-03 6 B 2000-01-04 7 B 2000-01-05 8 B 2000-01-06 9 B 2000-01-07 10 B 2000-01-08 12 B 2000-01-09 11 B 2000-01-10 9 B 2000-01-11 8 B 2000-01-12 9 B 2000-01-13 7 C 2000-01-02 3 C 2000-01-03 2 C 2000-01-04 4 C 2000-01-05 3 C 2000-01-06 4 C 2000-01-07 5 C 2000-01-08 4 C 2000-01-09 3 C 2000-01-10 4 C 2000-01-11 6 C 2000-01-12 6 C 2000-01-13 5 ; run; data have; set have; week = intnx('week', date, 0, 'e'); format week yymmdd10.; run; I want to calculate correlation between every pair of IDs' prices: i.e., correlation between prices of (A,B), (B,C), (A,C), over each week. The result I want looks like the following: data want; input id1 $ id2 $ week :yymmdd10. corr 5.; format week yymmdd10.; datalines; A B 2000-01-08 0.3 A C 2000-01-08 0.5 B C 2000-01-08 0.4 A B 2000-01-15 0.2 A C 2000-01-15 0.4 B C 2000-01-15 0.5 ; run; Is there a way to do this efficiently, especially when I have A, B, C, ..., Z and many weeks? ... View more

I have a CSV file in which date variables look weird. The following picture is what I see when if I open the CSV in Excel. Also, many rows refused to change when I attempted to format the column into a single date format in Excel. Please see the attached file as an example. When I import it into SAS, I use data date_format; infile "&in_path\date_format_issue.csv" delimiter = ',' dsd missover lrecl = 32767 firstobs = 2; informat id 20. date anydtdte.; format date yymmdd10.; input id date; run; When I check the number of unique IDs per month using proc sql; create table temp as select distinct intnx('month', date, 0, 'e') as month format yymmdd10., count(unique id) as num from date_format group by month order by month; quit; I found that the date variable is causing problems. The numbers are incorrect because dates are corrupt. For example, there should be 1300+ unique IDs in Jan, but I got only 588. When my friend uses his computer to open the CSV file, he was able to format the date in Excel correctly. Unfortunately I couldn't do this in my computer. How can I import the dates accurately into SAS?        ... View more

I have a folder of CSV files named based on daily dates, such as "data_20150101". I am trying to import them and append to one single dataset. My current code is: %macro import(start, end); %let start = %sysfunc(inputn(&start, anydtdte9.)); %let end = %sysfunc(inputn(&end, anydtdte9.)); %let dif = %sysfunc(intck('day', &start, &end)); %do i = 0 %to &dif; %let date = %sysfunc(intnx('day', &start, &i, 'e'), yymmddn8.); data data_new; infile "&in_path\data_&date..csv" delimiter = ',' dsd missover lrecl = 32767 firstobs = 2; informat var1 $10. var2 10.; input var1 var2; run; proc append base = want data = data_new; run; %end; %mend; %import(20150101, 20151231); When I run the code, nothing happens. I got a message:  WARNING: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks. ... View more