Skip to content

Instantly share code, notes, and snippets.

@shivallan

shivallan/Task_1

Created April 27, 2015 11:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save shivallan/5771ad5789ea36a85d5b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save shivallan/5771ad5789ea36a85d5b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Task_1
* Creating of input tables;
filename table_1 'C:\Users\Andrey.Belov\Documents\My SAS Files\My_tasks\1\1_table_1.txt';
filename table_2 'C:\Users\Andrey.Belov\Documents\My SAS Files\My_tasks\1\1_table_2.txt';
filename table_3 'C:\Users\Andrey.Belov\Documents\My SAS Files\My_tasks\1\1_table_3.txt';
libname tasks 'C:\Users\Andrey.Belov\Documents\My SAS Files\My_tasks\1';
data tasks.table_1;
infile table_1;
input ID $ 1-10 Name $ 12-22 Address $ 24-33;
run;
data tasks.table_2;
infile table_2;
input ID $ 1-10 Name $ 12-22 Sign $ 24-32 Made $ 34 MDate 36-39;
run;
data tasks.table_3;
infile table_3;
input INN $ 1-10 Name $ 12-22 YIncome 24-30;
run;
* Printing of input tables for checking;
proc print data = tasks.table_1 noobs;
title 'Passports';
run;
proc print data = tasks.table_2 noobs;
title 'Car_owners';
run;
proc print data = tasks.table_3 noobs;
title 'Income';
run;
* Creating of table for Task#1 using SQL;
proc sql;
create table tasks.SQL_Task_1 as
select distinct table_1.Name
from tasks.table_1 JOIN
tasks.table_2 ON table_1.Name = table_2.Name JOIN
tasks.table_3 ON table_2.Name = table_3.Name;
quit;
proc print data = tasks.SQL_Task_1 noobs;
title 'SQL_TASK_1';
run;
* Creating of table for Task#1 using DATA STEP;
proc sort data = tasks.table_1 out = work.table_1_names_sorted nodupkey;
by Name;
run;
proc sort data = tasks.table_2 out = work.table_2_names_sorted nodupkey;
by Name;
run;
proc sort data = tasks.table_3 out = work.table_3_names_sorted nodupkey;
by Name;
run;
data tasks.SAS_Task_1;
merge work.table_1_names_sorted (in = n1 keep = Name)
work.table_2_names_sorted (in = n2 keep = Name)
work.table_3_names_sorted (in = n3 keep = Name);
by Name;
if n1 and n2 and n3;
run;
proc print data = tasks.SAS_Task_1 noobs;
title 'SAS_TASK_1';
run;
* Creating of table for Task#2 using SQL;
proc sql;
create table tasks.SQL_Task_2 as
select distinct table_2.Name, Sign
from tasks.table_2 right join
tasks.table_3 on table_2.Name = table_3.Name
where (YIncome < 10000) and (Made = 'F') and (MDate > 2006);
quit;
proc print data = tasks.SQL_Task_2 noobs;
title 'SQL_TASK_2';
run;
* Creating of table for Task#2 using DATA STEP;
proc sort data = tasks.table_2 out = work.table_2_names_sorted;
by Name;
run;
proc sort data = tasks.table_3 out = work.table_3_names_sorted;
by Name;
run;
data work.SAS_Task_2;
merge work.table_2_names_sorted (in = n2)
work.table_3_names_sorted (in = n3);
by Name;
*where (work.table_3_names_sorted.YIncome < 10000) and (work.table_2_names_sorted.Made = 'F') and (work.table_2_names_sorted.MDate > 2006);
if n2 and n3;
run;
data tasks.SAS_Task_2 (keep = Name Sign);
set work.SAS_Task_2;
where (YIncome < 10000) and (Made = 'F') and (MDate > 2006);
run;
proc print data = tasks.SAS_Task_2 noobs;
title 'SAS_TASK_2';
run;
* Creating of table for Task#3 using SQL;
proc sql;
create table tasks.SQL_Task_3 as
select ID, Name, Made
from tasks.table_2
order by Made;
quit;
proc print data = tasks.SQL_Task_3 noobs;
title 'SQL_TASK_3';
run;
* Creating of table for Task#3 using DATA STEP;
proc sort data = tasks.table_2 out = work.foreign_cars (keep = ID Name Made);
by Made;
run;
proc print data = work.foreign_cars;
by Made;
id Made;
title 'SAS_TASK_3';
run;
* Creating of table for Task#4 using SQL;
proc sql;
create table tasks.SQL_Task_4 as
select distinct ID, Name
from tasks.table_2
group by ID
having count(ID) > 1
;
quit;
proc print data = tasks.SQL_Task_4 noobs;
title 'SQL_TASK_4';
run;
* Creating of table for Task#4 using DATA STEP;
proc sort data = tasks.table_2 (keep = ID Name) out = work.table_2_id_sorted nodupkey dupout = tasks.SAS_Task_4_dups;
by ID;
run;
proc print data = tasks.SAS_Task_4 (keep = ID Name) noobs;
title 'SAS_TASK_4';
run;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment