Problem with Unpacked Files and truncated IDs (e.g., 4372897E13)

[akline1]

I'm pretty new to Canvas and just set up CanvasDataCLI and unpacked my synced files  to txt. Problem is that I have truncated IDs (e.g., 4372897E13). I know that I can open in Excel and change the format but, I'm trying to manage without having to open up each unpacked file and format. I'm using SAS and reading the txt files in, but just can't get re-format to show the entire ID. Any suggestions?

[James]

I don't have easy access to SAS to verify this, but a trick that can be used with Excel is to tell it that the numbers are actually text rather than numbers. That way, it keeps the leading 0's in there as well.

Have you tried telling SAS to import those columns as text? Not sure on how you're importing them, so that might mean not using the $ in the import or specifying it as text.

SAS allows you to tell it how to store and how to display numbers, so I'd probably go that route over choosing the text. You can set a default length of numbers as well. Unfortunately, it's been too long since I've used SAS to be much more help.

But it doesn't matter (other than it looks ugly) that SAS considers them as scientific notation as long as there is enough precision to keep all of the digits. So, if you're telling it the numbers are floating point and not giving it enough precision, there will be other issues.

[akline1]

Hey James,

Thanks for the reply. I've done some more tinkering and figured out a few things. You were correct about it not mattering aside from being ugly. This being my first time pulling the Canvas data and figuring out how to tie it together, I wanted to be able to see the full id strings so I could quickly eyeball tables to make sure I'm getting the right merges. Below is a snippet of code I used for the enrollment_dim table:

data test;

       retain id2 root_account_id2 course_section_id2 role_id2 course_id2 user_id2;

       set enroll_dim;

       format id2 root_account_id2 course_section_id2 role_id2 course_id2 user_id2 bestd24.;

            %macro convert(id);

                 &id.2=put(&id.,z24.);

                 drop &id;

                 rename &id.2 = &id.;

            %mend;

            %convert(id);

            %convert(root_account_id);

            %convert(course_section_id);

            %convert(role_id);

            %convert(course_id);

            %convert(user_id);

run;

Abe