Hello, Your code gives 15 observation in the output because you have the code: if last.Subj then output; inside the do loop therefore when the condition is true it writes 5 observations to the output , one for every step of do loop. A number of solutions have been presented here therefore you have a choice ... View more

Hello, Maybe this is what you are looking from: PROC SQL NOPRINT;      CREATE TABLE Need AS            SELECT *            FROM sashelp.class AS A            WHERE name in                 (SELECT name                 FROM sashelp.class                 WHERE                      age in (14, 15, 16)                     AND (sex = 'M')); QUIT; ... View more

Hello, This will do it: proc datasets library=test memtype=catalog nolist; change rest=eee; quit; ... View more

Hello, This works only if one component belongs to one parent. Data ProductStructure;   Input @1 Parent $7. @9 Component $7. @17 Alternative $2. Percentage_Quantity;   dummy=Parent!!"_"!!Component!!"_"!!Alternative;   Datalines; 2000000 4000000 01 0.4 2000000 4600000 01 0.6 2000000 4000009 02 0.4 2000000 4600000 02 0.6 2000000 4000000 03 0.3 2000000 4600000 03 0.7 4600000 4500000 01 1 4600000 4500000 02 0.8 4600000 4500001 02 0.2 4600000 4500001 03 0.5 4600000 4500004 03 0.5 3000000 4000000 01 0.5 3000000 4700000 01 0.5 3000000 4000009 02 0.6 3000000 4700000 02 0.4 4700000 4500000 01 1 4700000 4500000 02 0.8 4700000 4500001 02 0.2   ; Run; proc sql; create table int as ( select x.parent, coalesce(y.inicomp, x.component) as component,x.alternative, coalesce(Percentage_Quantity*iniperc,x.Percentage_Quantity) as percent, x.component as subcomp from ProductStructure (drop=dummy) x left join ( select b.parent as inipar,b.component as inicomp, b.alternative as inialt, b.Percentage_Quantity as iniperc from ProductStructure a , ProductStructure b where a.component=b.parent and a.alternative=b.alternative) y on x.component=y.inipar where x.parent not in (select b.parent from ProductStructure a , ProductStructure b where a.component=b.parent and a.alternative=b.alternative)) order by parent, alternative, inialt ; quit; data want; retain  Perc_Ini Comp_Ini Perc_Ini ;*keep initial values; set int; by parent alternative;*data already from sorted from the sql syntax; BoM_ID=catx("_",parent,alternative,subcomp,alternative); output;*write the current record to data set; percsum+percent; if first.alternative then /*set initial values;*/ do;   Comp_Ini=component;   Perc_Ini=percent;   percsum=Perc_Ini; end; if round(percsum,0.1)=1 and not last.alternative then /*write a second line to data set if sum of percents is 1 and not the last record within group*/ do;   component=Comp_Ini;   percent=Perc_Ini;   output;   percsum=Perc_Ini; end; drop Perc_Ini Comp_Ini Perc_Ini percsum subcomp; run; ... View more

Hello, One solution: data have; input PRODUCT $ VEHICLE_TYPE $ VEH_YEARS CLAIM_NOS CLAIM_COST; datalines; MFF . 234 198 1203940 MFA . 223 134 776098 . VAN 50 22 200010 . TRUCK 177 150 1200023 . SPECIAL 23 1 12039 . CAR 207 159 567966 ; data want; length factor level $20 ; set have; if missing(VEHICLE_TYPE) then do;   level=product;   factor=vname(product); end; else if missing(product) then do;   level=VEHICLE_TYPE;   factor=vname(VEHICLE_TYPE); end; drop PRODUCT VEHICLE_TYPE; run; ... View more

Hello, Within the format procedure http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#format-overview.htm it is stated: User-defined informats read only character data. They can convert character values into real numeric values, but they cannot convert real numbers into characters that's the reason for the error message you got when trying to to define an informat for converting numbers into characters. Best ... View more

Hello, One solution: data have ; infile datalines truncover; input ID $10. SEQNUM NAME $ ACTION & $20.; datalines; 470010002 2 abc NOT CHANGED 470010002 2 abc not done 470010002 4 def NOT CHANGED 470010002 4 def NOT CHANGED 470010002 7 jkl NOT CHANGED 470010007 19 xyz INTERRUPTED 470010007 19 xyz not done 470010010 10 pqr not done 470010012 5 suv NOT CHANGED 470010012 6 lmn INTERRUPTED 470010012 6 lmn INTERRUPTED 470010012 6 lmn INTERRUPTED 470010012 6 lmn INTERRUPTED 470010012 6 lmn not done ; proc sql; select a.* from have a left join (select id, SEQNUM, NAME from have where trim(action)='not done') b on a.id=b.id and a.SEQNUM=b.SEQNUM and a.NAME=b.NAME where b.id is missing; quit; ... View more

Hello, A proc sql solution: data have; infile datalines truncover; input id Name $ date ddmmyy10.; format date ddmmyy10.; datalines; 11 john 12/04/2014 22 Matt 26 miac 22/01/2013 ; proc sql; create table want as select *, case when date is missing or date ge  '15May2014'd   then 0 else 1 end as indicator from have; quit; ... View more

Hello, proc sql;   create table want   as select db1.*,   case   when db1.id = db2.id and db2.date >= db1.start_date and db2.date <= db1.end_date then 1   else .   end as flag   from db1 left join db2 on db1.id = db2.id; quit; ... View more