Hi All,
I have two input files (1st file - contains 2000 records & 2nd File contains 5000 records) and both the files have ID, status_number as common, the percent field is in 2nd file. I have used concatenate funtion to club ID & status_number and named it as ID_Status to pull the percent field from 2nd file, but not receving accurate result. I am only interested in sales type.
Screen shots are attached
1st File | |||
SI No | ID | status_number | Type |
1 | 123 | 45678 | sales |
2 | 123 | 34567 | cost |
3 | 123 | 78795 | sales |
4 | 123 | 98765 | invoice |
5 | 123 | 12345 | sales |
6 | 234 | 56872 | sales |
7 | 234 | 76549 | cost |
8 | 234 | 88887 | sales |
9 | 234 | 66666 | invoice |
10 | 234 | 11111 | invoice |
2nd File | ||||
SI No | ID | status_number | Type | Percent |
1 | 123 | 45678 | sales | 0 |
2 | 123 | 34567 | cost | 100 |
3 | 123 | 78795 | sales | 100 |
4 | 123 | 98765 | invoice | 100 |
5 | 123 | 12345 | sales | 100 |
6 | 234 | 56872 | sales | 100 |
7 | 234 | 76549 | cost | 0 |
8 | 234 | 88887 | sales | 0 |
9 | 234 | 66666 | invoice | 100 |
10 | 234 | 11111 | invoice | 100 |
Output
SI No | ID | status_number | Type | Percent | Ouput |
1 | 123 | 45678 | sales | 0 | Error |
3 | 123 | 78795 | sales | 100 | Error |
5 | 123 | 12345 | sales | 100 | Error |
6 | 234 | 56872 | sales | 100 | Correct |
8 | 234 | 88887 | sales | 0 | Correct |
Regards,
Shaheen
data have;
infile cards expandtabs;
input SINo ID status_number Type $ Percent;
cards;
1 123 45678 sales 0
2 123 34567 cost 100
3 123 78795 sales 100
4 123 98765 invoice 100
5 123 12345 sales 100
6 234 56872 sales 100
7 234 76549 cost 0
8 234 88887 sales 0
9 234 66666 invoice 100
10 234 11111 invoice 100
;
run;
proc sql;
create table want as
select *,case
when count(*) gt 2 then 'ERROR '
else 'CORRECT'
end as flag
from have
where type='sales'
group by ID;
quit;
I added a join to the solution above:
data one;
infile cards dsd;
input SINo ID$ status_number$ Type$;
cards;
1,123,45678,sales
2,123,34567,cost
3,123,78795,sales
4,123,98765,invoice
5,123,12345,sales
6,234,56872,sales
7,234,76549,cost
8,234,88887,sales
9,234,66666,invoice
10,234,11111,invoice
;
run;
data two;
infile cards dsd;
input SINo ID$ status_number$ Type$ percent;
cards;
1,123,45678,sales,0
2,123,34567,cost,100
3,123,78795,sales,100
4,123,98765,invoice,100
5,123,12345,sales,100
6,234,56872,sales,100
7,234,76549,cost,0
8,234,88887,sales,0
9,234,66666,invoice,100
10,234,11111,invoice,100
;
run;
proc sql;
create table output as
select a.sino,a.id,a.status_number,a.type,b.percent,
case
when count(*) < 3 then 'Correct'
else 'Error' end as Output
from one a left join
two b on
a.id = b.id and
a.status_number = b.status_number
where a.type = 'sales'
group by a.id
order by a.sino,a.id;
Hi All,
I will try both the solutions. If i have face any difficulties, will get back. Thank you.
Regards,
Shaheen
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.