topic Re: How do I create below SAS Report? in SAS Programming

How do I create below SAS Report?

MKS2204 — Mon, 03 Apr 2017 12:50:39 GMT

Hi,

I came accross a report creation I am finding difficulties to. I have below data and I need a report (layout given). Not getting any idea how to achieve that.

I have data for 16 student for Grade=1.

Value of Each digit of the checkvar is and edit code and position 1 to maxitem is a an item no.

e.g. checkvar = 401234562253 , at item 1 we have the value of edit code as 4, and item 2 is 0, item 3 is 1 and so on.

max(maxitem) is 20 ( thats why in desired layout I have taken max item as 20.

Any edit code apart from 0,1,2,3 falls under "others" in desired output.

Student	Grade	Form	Checkvar	maxitem	Editcode
1	1	1	1234567892112	15	each digit in checkvar is an item and the value is called Edit Code
2	1	2	102310203524	12	each digit in checkvar is an item and the value is called Edit Code
3	1	3	401234562253	20	each digit in checkvar is an item and the value is called Edit Code
4	1	4	1265431822345	13	each digit in checkvar is an item and the value is called Edit Code
5	1	1	1234567892322	15	each digit in checkvar is an item and the value is called Edit Code
6	1	2	102310203112	12	each digit in checkvar is an item and the value is called Edit Code
7	1	3	401234562	20	each digit in checkvar is an item and the value is called Edit Code
8	1	4	12654318233	13	each digit in checkvar is an item and the value is called Edit Code
9	1	1	1023102033421	15	each digit in checkvar is an item and the value is called Edit Code
10	1	2	4012345621	12	each digit in checkvar is an item and the value is called Edit Code
11	1	3	1265431820232	20	each digit in checkvar is an item and the value is called Edit Code
12	1	4	12345	13	each digit in checkvar is an item and the value is called Edit Code
13	1	1	72413521836	15	each digit in checkvar is an item and the value is called Edit Code
14	1	2	126740021203	12	each digit in checkvar is an item and the value is called Edit Code
15	1	3	12190142231331	20	each digit in checkvar is an item and the value is called Edit Code
16	1	4	1234011301233	13	each digit in checkvar is an item and the value is called Edit Code

Below is the output i need.:

Item	edit code=0	edit code=1	edit code=2	edit code=3	edit code=Others	Total
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Total

This is my first post to SAS community ever so I hope I make sense. Appreciate your help and guidance

I am on sas 9.4

Re: How do I create below SAS Report?

RW9 — Mon, 03 Apr 2017 13:13:32 GMT

Hi,

Sorry, no not clear to me at all. Start be dropping your test data, a couple of lines, into a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

And post it into a code window here ({i} above post). Then explain what each part of this code is, you talk about item 1 and item 2, but there is not "item" column in the data provided. Once I can understand the code then the report is what is called transposing - moving from long to wide, and you need to make some decisions based on duplicates etc.

Re: How do I create below SAS Report?

Reeza — Mon, 03 Apr 2017 13:17:03 GMT

Is CHECKVAR a string or number?

Either way, split this to a long format using CHAR() function. This will transpose it to a long format and then you can use PROC TRANSPOSE to flip it to structure that's more useful. I can't quite understand the remaining steps but this can get you started.

Data long;
 Set have;

Length editCategory $8.;
Num_chars = length(check_Var);

Do Item = 1 to num_chars;

EditCode = char(check_var, item);

If editCode not in ('1', '2', '3') then editCategory = 'Other';
Else editCategory = editCode;
Output;

End;

Keep student grade form editcode editCategory;

Run;

Re: How do I create below SAS Report?

MKS2204 — Mon, 03 Apr 2017 14:12:31 GMT

Hi,

I am sorry but I need to work on the method you mentioned to get the data in and also if I could not make myself clear on the requirements.

Below is the sample data I have. And I tried to devide check_var in multiple columns (items) and each item column have the value of edit code in it.

My requirement is to get the number of students with edit code 0 in item1 , edit code 1 in item1 , edit code 2 in item1, edit code 3 item1,edit code "others" in item1.

edit code 0 in item2 , edit code 1 in item2 , edit code 2 in item2, edit code 3 item2,edit code "others" in item2 for each row of my desired output .

number of rows in the output report is the maxitem.(in this case its 24).

"Total" is the total number of students sum accross row and column in output report.

data rawdata;

input student 1. grade $char2. form $char2. check_Var $char24. maxitem $char24.;

cards;

1030212320310231020323201123424

2030320123023102312810321213124

3030210123023107412310121213124

4030200123023102312710321213124

5030310123022107312310121213124

;

run;

data want;

set rawdata;

array Value_Item (24);

do i = 1 to dim(Value_Item);

Value_Item[i] = substr(check_Var,i,1);

end;

run;

Item	edit code=0	edit code=1	edit code=2	edit code=3	edit code=Others	Total
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Total

I hope I make sense this time.

Re: How do I create below SAS Report?

Reeza — Mon, 03 Apr 2017 14:52:07 GMT

I guess I don't understand. Post a fully worked exampke, include the solution for the sample data.

You can simplify this if needed.

Also, did you try the code?

Re: How do I create below SAS Report?

MKS2204 — Mon, 03 Apr 2017 15:03:21 GMT

Thanks Reeza, I will try to comeup with example. Yes I did try the code and now thinking how I can use that data to get my desired result.