%macro read_files (out=zip_file); %let FOLDER = C:\planinfo\IRS_DATA; filename _tree pipe "DIR /b &FOLDER *.txt "; data temp1; infile _tree pad recfm=v lrecl=1024; length file path $200; input file $ ; path=cats("&folder","\",file); if file = "WindowsCodecsRaw.txt" then delete; run; %Local i work_dsn n_ids; proc sql noprint; select count(*) into :n_ids from temp1;quit; %put n_ids=&n_ids; %Do i = 1 %To &n_ids; %Let last_n = %Eval(&i - 1); Proc Sql noprint; Select distinct path Into :filename Separated by ',' From temp1 (FirstObs=&i obs=1) ;Quit; %put filename=&filename; proc import datafile= "&filename" out=temp2 dbms=csv replace ; delimiter=',' ;getnames=yes ; run; %if &i=1 %then %do; %if %sysfunc(exist(&out)) %then %do; Proc Sql Noprint; Drop Table &out; Quit; %end; data &out; set temp2; run; %end; %else %do; Proc Append Base=&out Data=temp2 force; Run; %end; Proc Sql Noprint; Drop Table work.temp2; Quit; %end; %mend; %read_files; I'm utilizing the code to import all txt files to append to the first read file. It's receiving an error on the firstobs. Its reading the path correctly but for some reason I can't figure out why its not recreating the other 2 files. It seems to be replicating the first file. Any help would be appreciated. Here is the error below and a screenshot of my TEMP1 dataset.   ERROR: FIRSTOBS option > OBS option - no data to read from file WORK.TEMP1.     ... View more

I have a large data set that has multiple tiers to a C_ID. The C_ID serves as the foundation of the detail, from there a P_ID is associated and finally a segment_id. I'm trying to find the weighted average PART_C_D_PREMIUM of each P_ID that is associated to the parent C_ID by rolling up the segment_id's premiums.   I need to calculate weights based on frequency or number of segment_id's associated to each p_id and c_id. Not sure if a weighted mean is possible using this methodology, or if anyone has a better approach. If its not possible, what is the best way to find the mean premium and rolling up the segment_id's so that its just c_id and p_id with the corresponding means for each premium.  Example below of what I want my output to look like C_ID P_ID parentorg plan_type plan_name PART_C_D_Premium Mean H05  067 A  HM  BC 0 0 H15  006 A  LP AM 47,44,47 46   Here is my data set    data have; infile datalines dlm="09"x; input C_ID $ P_ID $ parentorg $ plan_type $ plan_name $ segment_id p_c_premium p_d_premium part_c_d_prem_combine; datalines; H05 067 A HM BC 1 0 0 0 H05 067 A HM BC 2 0 0 0 H10 237 H HM HG 1 0 0 0 H10 237 H HM HG 2 0 0 0 H15 006 A LP AM 1 0 47 47 H15 006 A LP AM 2 0 44 44 H15 006 A LP AM 4 0 47 47 H19 014 P HM PH 1 0 0 0 H19 014 P HM PH 2 25 0 25 H20 099 H HM HG 1 0 0 0 H20 099 H HM HG 2 0.6 22.4 23 H20 100 H HM HG 4 0 0 0 H20 100 H HM HG 5 19 0 19 H39 059 G LP GG 3 36.4 33.6 70 H39 059 G LP GG 4 35.9 39.1 75 H39 060 G LP GG 3 0 0 0 H39 060 G LP GG 4 0 0 0 H39 061 G LP GG 1 122.8 56.2 179 H39 061 G LP GG 2 102.8 56.2 159 H39 061 G LP GG 5 62.8 56.2 119 ; run;   ... View more

proc sql; create table TEST_STATUS (MEMB_ID num, PAD_FLAG char(1), DECILE num, DOS_YEAR num, PROGRESSION char(6), STATUS char(8)); insert into TEST_STATUS values(1231, 'N', 8, 2019,'STAYER',' DROPPED') values(1231, 'N', 8, 2020,'STAYER',' DROPPED') values(1241, 'Y', 9, 2019,'STAYER',' FOUND ') values(1241, 'Y', 9, 2020,'STAYER',' DROPPED') values(1251, 'N', 9, 2019,'STAYER',' FOUND ') values(1251, 'N', 9, 2020,'STAYER',' FOUND ') values(1261, 'N', 2, 2019,'STAYER',' DROPPED') values(1271, 'N', 7, 2019,'STAYER',' DROPPED') values(1271, 'N', 7, 2020,'STAYER',' DROPPED') values(1281, 'N', 9, 2019,'STAYER',' DROPPED') values(1281, 'N', 9, 2020,'STAYER',' DROPPED') values(1291, 'Y', 1, 2019,'STAYER',' FOUND ') values(1291, 'Y', 1, 2020,'STAYER',' FOUND ') values(1211, 'Y', 8, 2019,'STAYER',' NEW ') values(1211, 'Y', 8, 2020,'STAYER',' DROPPED') values(1201, 'Y', 9, 2019,'STAYER',' FOUND ') values(1201, 'Y', 9, 2020,'STAYER',' DROPPED') values(1301, 'Y', 1, 2019,'STAYER',' FOUND ') values(1301, 'Y', 1, 2020,'STAYER',' DROPPED') values(1311, 'Y', 9, 2019,'STAYER',' FOUND ') values(1311, 'Y', 9, 2020,'STAYER',' DROPPED') values(1331, 'N', 1, 2019,'STAYER',' DROPPED') values(1331, 'N', 1, 2020,'STAYER',' DROPPED') ; run; /* EXAMPLE OF LOGIC I NEED, this code does not work */ data WANT; set TEST_STATUS; if DOS_YEAR = 2019 and STATUS in ('NEW','FOUND') and DOS_YEAR = 2020 and STATUS in ('DROPPED') then TARGET=1; ELSE TARGET=0; if DOS_YEAR = 2019 and STATUS = 'DROPPED' and DOS_YEAR = 2020 and STATUS = 'DROPPED' then TARGET=1; ELSE TARGET=0; run; I'm trying to find a way to weed out non targeted membership. I need to exclude members with a status of 'NEW' or 'FOUND' in 2020. I'm also trying to target members where 'DROPPED' status or 'NEW','FOUND' status was in 2019 and contained a 'DROPPED' status in 2020. When i add multiple conditions it does not work. Any help would be appreciated. It's also importan to note there are scenarios where a member only shows up for 1 year, in this case I need to target where if member status in 2019 = 'DROPPED'  I need to target.    These are the results i'm looking for  MEMB_ID PAD_FLAG DECILE DOS_YEAR PROGRESSION STATUS TARGET 1231 N 8 2019 STAYER DROPPED 1 1231 N 8 2020 STAYER DROPPED 1 1241 Y 9 2019 STAYER FOUND 1 1241 Y 9 2020 STAYER DROPPED 1 1251 N 9 2019 STAYER FOUND 0 1251 N 9 2020 STAYER FOUND 0 1261 N 2 2019 STAYER DROPPED 1 1271 N 7 2019 STAYER DROPPED 1 1271 N 7 2020 STAYER DROPPED 1 1281 N 9 2019 STAYER DROPPED 1 1281 N 9 2020 STAYER DROPPED 1 1291 Y 1 2019 STAYER FOUND 0 1291 Y 1 2020 STAYER FOUND 0 1211 Y 8 2019 STAYER NEW 1 1211 Y 8 2020 STAYER DROPPED 1 1201 Y 9 2019 STAYER FOUND 1 1201 Y 9 2020 STAYER DROPPED 1 1301 Y 1 2019 STAYER FOUND 1 1301 Y 1 2020 STAYER DROPPED 1 1311 Y 9 2019 STAYER FOUND 1 1311 Y 9 2020 STAYER DROPPED 1 1331 N 1 2019 STAYER DROPPED 1 1331 N 1 2020 STAYER DROPPED 1 ... View more