sas code output

[sathya66]

Hi,

I am looking for below output but I am getting a different out put If I use below code

getting

CD	ORDER	id	NO	Cd_S
A	23812	51	156	A
A	23866	55	56	A
A	23769	262	149	A
A	23772	262	150	A
A	23817	262	151	A
A	23788	641	196	A
A	23840	641	197	A
A	23748	677	102	A
A	23816	677	103	A
A	23748	709	96	A
A	23753	709	97	A
A	23753	709	98	A
A	23837	720	108	A
A	23840	720	110	A
A	23795	835	223	A
A	23835	835	224	A
A	23855	835	225	A
A	23776	856	142	A
A	23758	864	135	A

wated below output

CD_s	ORDER	id	NO	cd
A	23812	51	156	A
A	23866	55	56	A
A	23769	262	149	A
A	23772	262	150	A
A	23817	262	151	A
A	23788	641	196	A
A	23840	641	197	A
A	23748	677	102	A
A	23816	677	103	A
A	23748	709	96	A
A	23753	709	97	A
A	23753	709	98	AA
A	23837	720	108	A
A	23840	720	110	A
A	23795	835	223	A
A	23835	835	224	A
A	23855	835	225	A
A	23776	856	142	A
A	23758	864	135	A

data test;
length a $200;
set have;
by order NO;
if first.order then do;
CD = Cd_S;
end;
else do;
CD = cats(CD,cd_S);
end;
run;

[PaigeMiller]

Please explain the logic used to obtain data set 2 from data set 1.

May I also recommend that in all of your future posts, the logic should be explained.

[sathya66]

proc sql;
create table Pre as    
select
catx('-',ID,DATE) as ORDER,
catx('-',ID,NO) as U_ORDER,
a.id,
a.NO,
b.DATE,
a.method,
case 
when a.bd = 'LWa' then 'l w'
else a.bd end as bd,
case 
when b.code like '%I%' then 'Pl'
when b.code like '%A%' then 'At'
when b.code like '%R%' then 'Rt'
else 'Other' end as Cd,
case 
when b.code like '%I%' then 'P'
when b.code like '%A%' then 'A'
when b.code like '%R%' then 'R'
else 'O' end as Cd_S,
sum(a.p_demand) as p_demand,
sum(a.p_items) as p_items,
sum(a.po_demand) as po_demand,
sum(a.po_items) as po_items
from ta.demand A
left outer join Os.mart B
  on a.id = b.id
and a.no = b.no
and a.no = b.no
where b.DATE >= 1250101
  and ind = 0
group by 1,2,3,4,5,6,7
order by 1,2,3,4,5,6,7);
quit;



proc sql;
create table have as
select
ORDER,
id,
NO,
DATE,
Cd_S
from Pre
order by 1,2,3,4,5;
quit;


proc sort data=have;
by ORDER NO;
run;
data test;
length a $200;
set have;
by order NO;
if first.order then do;
CD = Cd_S;
end;
else do;
CD = cats(CD,cd_S);
end;
run;

[PaigeMiller]

Thanks, but I asked you to explain the logic you want to use, this doesn't explain, and doesn't answer my question. (In case its not clear, explain means write words to describe the steps to go from data set 1 to data set 2)

[sathya66]

ok, If order and ID have unique values then I need  first.Cd_S=A if not Cd_S=AA  like below

A	23753	709	97	A
A	23753	709	98	AA

[PaigeMiller · Accepted solution]

/* UNTESTED CODE */
data want;
    set have;
    length cd_s $ 2;
    by order id;
    if not first.id then cd_s='AA';
    else cd_s='A';
run;

If you want tested code, please provide data in usable format, which is working SAS data step code (examples and instructions).

[sathya66]

data a;

input cd_s$ order id no;
datalines ;

A 23812 51 156
A 23866 55 56
A 23769 262 149
A 23772 262 150
A 23817 262 151
A 23788 641 19
A 23840 641 197
A 23748 677 102
A 23816 677 103
A 23748 709 96
A 23753 709 97
A 23753 709 98
A 23837 720 108
A 23840 720 110
A 23795 835 223
A 23835 835 224
A 23855 835 225
A 23776 856 142
A 23758 864 135
A 23803 864 136
A 23833 864 137
A 23879 864 139
A 23765 1652 47
A 23786 1652 48
;
run;

proc sort data=a out= have;
by order id;
run;

data want;
    set have;
    by order id;
    if not first.id then cd_s='AA';
run;

[PaigeMiller]

@sathya66 

When I first posted the code for DATA WANT; I left two lines out, that is now corrected in my reply above, but you need to modify your code to show those changes I made.

[Kathryn_SAS]

I changed your BY variables and used a RETAIN statement. Will the code below give you the desired output?

proc sort data=have;
by order id;
run;

data test;
length cd_s $200;
retain cd_s;
set have;
by order id;
if first.id then do;
CD_s=cd;
end;
else do;
CD_s = cats(CD,cd_S);
end;
run;