Hello,   I am trying to iterate through my variables and assign a value of 0 based on the previous variable that corresponds to that quarter and year.   Here is a snippet of the data: data have; infile datalines4 dlm='|' missover dsd; input id q1_2018_units q1_2018_stdunits q2_2018_units q2_2018_stdunits q3_2018_units q3_2018_stdunits q4_2018_units q4_2018_stdunits; datalines; 1|214252|214252|149115||50000|50000|0|| 2|12||0||50000||0|| 3|21252|21252|||5000|.|0|| 4|214252|.|0||5000|5000|0|| 5|2252|.|14115||500|500|0|| ; run;   I want to assign a value of 0 to stdunits based on if there is a 0 in the units column for each corresponding quarter. That way I can pick out the 0 cases and compute a stdunit for those that aren't 0. To note, I have other quarters and year combinations but if I can apply to this subset then I can apply elsewhere.   For ID 2, there should be a . for q1_2018_stdunits and q3_2018_stdunits but a 0 value for q2_2018_stdunits and q4_2018_stdunits.   I was thinking of creating a var list, like below but for each quarter/year combination, to separate quarters to make it easier to go through? /*GET LIST OF UNIT VARIABLES*/ PROC SQL NOPRINT; CREATE TABLE VAR_NAMES AS SELECT NAME FROM DICTIONARY.COLUMNS WHERE LIBNAME = 'WORK' AND MEMNAME = 'HAVE' AND NAME CONTAINS '_units'; SELECT COMPRESS(NAME) INTO :VAR_LIST SEPARATED BY " " FROM VAR_NAMES; SELECT COUNT(NAME) INTO :NUM SEPARATED BY " " FROM VAR_NAMES; QUIT; I think the next step would be to create an array and do loop but I'm not sure what the parameters are and what type of do statement? I also saw whichn but I have no experience with that function. data want; set have; ARRAY X[*] &VAR_LIST; array Y[*] &VAR_LIST1; ??? I then want to iterate through each stdunit to drop out cases where stdunit is missing so I can just examine id's with missing stdunits that require computation.   Any help would be much appreciated... ... View more

Hi SAS Community,   I am importing pipe delimited .txt and .csv files into SAS and want to run a series of checks: 1. Count the number of records 2. Count the number of distinct records for ID 3. Whether the variables match a list of set variables 4. Are the values expected or in the correct format   Is there a way to output 1 dataset with all this information? As it currently stands,I produce these results into a text file that are the results of multiple proc frequencies and proc sql. These are my expected variables: data VARS; input vars $; datalines; ID BDAY GENDER ADDRESS1 ADDRESS2 CITY STATE ZIP ZIP4 COUNTY_CODE ; run; This is my sample data: data sample; infile datalines dlm="|" missover dsd; input ID $2 BDAY $10 GENDER $ ADDRESS1 $ ADDRESS2 $10. CITY $ STATE $ ZIP $ ZIP4 $ COUNTY_CODE $; datalines; A1|20200420|M|123 Main St.|Suite 201|Juneau|AK|99802||02112 B2|4/20/2020|Male|124 Main St.||Juneau|AK|99802|Juneau C3|4/20/2020|M|125 Main St.||Juneau|AK|99802||02112 4-1|20200420|Male|126 Main St.|Suite 101|Juneau|AK|99802||Juneau ; run;  Conditions: ID:should be alpha numeric and 2 characters BDAY: should be YYYYMMDD format Gender: should be 1 characters Address1 and Address 2 should be character City should character State should be 2 characters zip should be 5 characters zip4 should be no more than 4 characters  county should be the fips code which is 5 characters   Results: Line 1: accurate and meets all criteria Line 2: dob is not in correct format, gender is not 2 characters, does not have zip4 variable, county does not have 5 character Line 3: dob is not in correct format Line 4: id is not alpha numeric with no special characters, and gender is not 2 characters     So far I have: /*Variables match list? */ proc sql; create table plan_vars as select strip(upcase(name)) as vars from sashelp.vcolumn where libname='WORK' and memname =SAMPLE; quit; proc sql; create table comparevar_&file. as select a.*, b.*, case when a.vars = b.varsm then 'Match' else 'No' end as var_match from VARS as a full join plan_vars(rename=(vars=varsm)) as b on a.vars=b.varsm; quit; /*output records */ ods rtf file="&output.\FileReview.rtf"; ods noptitle; options nodate nonumber; proc sql; create table ctrltot_&file. as select count(*) as Total_number_of_records, count(distinct ID) as Total_number_of_unique_ID, count (ID) as tot_ID from sample; quit; proc freq data = sample; tables _all_; format _numeric_ _character_ $miss.; run; proc freq data = ctrltot_&file.; tables _all_; run; proc freq data=comparevar_&file.; tables _all_; where var_match = 'No'; run;   ... View more