topic Put a specifik value in a cell to differents row and coulumns in SAS Programming

Put a specifik value in a cell to differents row and coulumns

melhaf — Tue, 19 Apr 2022 09:53:52 GMT

Hi,

I have a data set who looks like this:

x y z w q

A 1 2 3 4 5
B 6 7 8 9 10
C 11 12 13 14
D 15 16 17 18

E 19 20 21 22 23

And I want to have like this:

x y z w q (col) (row)

A 1 2 3 4 5 101 202
B 6 7 8 9 10 ... ...
C 11 12 13 14 -- --
D 15 16 17 18... --

E 19 20 21 22 23 ... .....

so when A = x (value 1) we have col = 101 and row = 202

and when A = y (value: 2) we have col = ... and row ...

beacuse all of the values (1, 2, 3 ... 23) is a statistical values that I have calculated earlier in the code. (Sum, number of events, etc..) so and all of them is made by a proc sql and grouped by 1. i tried to do like this

proc sql;
create table varden as
select distinct
category,
count(...) as NumberOfEvents,
sum(....) as GrossLossAmount,
max(.....) as MaxRapportsumma, /* Maximum single loss */
(case
when aaa= 0 then 0
else count(LossAdjustments)
end) as NumberOfEvents
from blah
group by 1
;quit;

proc sql;
create table colrow
(col num(4),
row num(4),
id num(4)
)
;
insert into colrow (col, row, id)
values (0010, 0110, 0000)
........

.....

;
quit;

and then I don't know join them togheter? and then do something like

proc sql;
create table ____matrix as
select *,
(case
when 'A'='x' then col='0010' and row='0110'
when ....
uptil [64 times.}
else .
end)
from final
;quit;

but no.. I want to export this later to an xlm file

Re: Put a specifik value in a cell to differents row and coulumns

andreas_lds — Tue, 19 Apr 2022 10:28:48 GMT

Please post data in usable form, a dataset like

   x  y  z  w  q
A  1  2  3  4  5 
B  6  7  8  9 10
C 11 12 13 14
D 15 16 17 18
E 19 20 21 22 23

can't exist, because every variable / column needs a name.

Where do the numbers 101 and 202 come from?

Please show the complete result you expect and explain the logic to be applied.

Re: Put a specifik value in a cell to differents row and coulumns

melhaf — Tue, 19 Apr 2022 10:44:31 GMT

I thought I could use a hypotetic data set. But let's say I have this then

And i want all of this be printed out in a xls file later.

But It HAS To be in a specifik order. In a coordinat system. Like; A suv in Asia has to be in col = 101 and row= 202 so I want to add two columns in this cars dataset. "col" and "row"

but how can I call a "cell" in this dataset and give them a col and row namE?

Re: Put a specifik value in a cell to differents row and coulumns

andreas_lds — Tue, 19 Apr 2022 11:21:43 GMT

@melhaf wrote:

...

but how can I call a "cell" in this dataset and give them a col and row namE?

Sorry, but this is not the way sas datasets are used. Dataset are like tables in a database, not like sheets in excel.

You should really use data steps to post the data you have and what you need.