I am not totally clear but new variables can be made through case statement like  case when delay = -1 or delay = 0 then date else whatever end as newvariable where clause what you have mentioned should work      ... View more

Another way data have1; infile datalines dsd DLM='09'x; input Pol Category $ premium; datalines; 3 g 10 6 g 3 9 h 7 12 g 20 15 g 12 18 g 100 ; data have2; infile datalines dsd DLM='09'x; input Pol Category $ premium; datalines; 3 x 4 6 g 2 102 g . 15 z 12 12 g 15 18 g 200 ; proc sql; select case when a.pol is missing then 'NA' else put(a.pol,8.) end as pol, case when a.category is missing then 'NA' else a.category end as category, case when a.premium is missing then 'NA' else put(a.premium,8.) end as premium, case when b.pol is missing then 'NA' else put(b.pol,8.) end as pol, case when b.category is missing then 'NA' else b.category end as category, case when b.premium is missing then 'NA' else put(b.premium,8.) end as premium from have1 as a full join have2 as b on a.pol = b.pol; ... View more

there could be simpler way to do this. This is one way to do this data have1; infile datalines dsd DLM='09'x; input Pol Category $1.; datalines; 3 g 6 g 9 g 12 g 15 g 18 g 1 x 4 x 7 x 10 x 13 x 16 x 2 y 5 y 8 y 11 y 14 y 17 y ; data have2; infile datalines dsd DLM='09'x; input Pol Category $1.; datalines; 3 x 6 g 102 g 12 g 15 g 18 g 1 x 4 x 7 g 10 x 13 x 16 x 21 y 5 y 8 y 11 y 14 y 17 y ; proc sql; create table want as select coalesce(x.category, y.category, z.category) as category , intersection, only_table1, only_table2 from (select a.category, count(a.category) as intersection from have1 as a inner join have2 as b on a.pol = b.pol and a.category = b.category group by a.category)x full join (select a.category, count(a.category) as only_table1 from have1 as a left join have2 as b on a.pol = b.pol and a.category = b.category where b.pol = . group by a.category)y on x.category =y.category full join (select b.category, count(b.category) as only_table2 from have1 as a right join have2 as b on a.pol = b.pol and a.category = b.category where a.pol = . group by b.category)z on x.category =z.category; ... View more

@Patrick answers sums up everything .  I just wants to add couple of things on conversion of Oracle to Teradata part. I have been on project where in moved from Oracle to Teradata.  We had to change lot of scripts especially related to explicit pass through. If you know basic SQL then Oracle SQL or Teradata SQL should not big deal. Many functions are different in both the SQL, but google is your friend here. One thing you have to be careful is how index are created and used in both of this databases, which is really big deal. In Teradata, primary index should be designed carefully otherwise data skewing  will happen. Data skew causes space wastage and also weakens the parallel processing capabilities of Teradata. Another difference for explicit pass through is Oracle is case sensitive and Teradata is not case sensitive ... View more

it is little bit confusing. can you please illustrate with simple example with input you have and output you want to see. ... View more

try this proc sql; select old from want order by scan(old, -1,'_'); ... View more

just wondering to know, whether you or your group has right to delete records    delete from &OUTTABLE.Tn_3 where subs_id in (select subs_id from &OUTTABLE.Tn_1); ... View more

you are right @novinosrin, fixed it  ... View more

or something like this data want; set have; array new(*) $30 student001-student003; array firstname(*) $ FirstName001-FirstName003; array lastname(*) $ lastName001-lastName003; do i = 1 to dim(firstname); if substr(vname(firstname[i]),10,3) = substr(vname(lastname[i]),9,3) then new{i}=cats(' ',firstname{i},lastname{i}); end; run; /*or if you have counterpart for every first and last name then*/ data want; set have; array new(*) $20 student001-student003; array firstname(*) $ FirstName001-FirstName003; array lastname(*) $ lastName001-lastName003; do i = 1 to dim(firstname); new{i}=cats(' ',firstname{i},lastname{i}); end; run;   ... View more

are there only few columns then    student001=catx(' ', firstname001, lastname001); student002 =catx(' ',firstname002, lastname002) student003 = catx(' ', firstname003,lastname003) ... View more