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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.