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
MK
Hi,
Sorry, no not clear to me at all. Start be dropping your test data, a couple of lines, into a datastep:
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.
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;
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.
MK
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.